How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Pete Your Own Question
Pete
Pete, Computer Engineer
Category: Mac
Satisfied Customers: 17021
Experience:  Over 16 years of computer experience, CISCO certified
30970729
Type Your Mac Question Here...
Pete is online now
A new question is answered every 9 seconds

I have spreadsheet in excel 2011 for mac with a table of 500

This answer was rated:

I have spreadsheet in excel 2011 for mac with a table of 500 airline flights, many of which are duplicates. I will call this table A. I have copied the table and pasted it in columns to the right. After doing so, I ran the function to remove duplicates. I will call this table B. Now that table B has no duplicates, I want to enter the baggage claim number for each flight in table B.

Here is my problem. I want each flight entry in table A to query table B until it finds the matching flight number, then copy the baggage claim for that flight into a cell adjacent to the flight number in table A

example:

table A baggage claim table B baggage claim
AA 1234 AA 1234 B1
AA 1234 DL 1342 E9
AA 1234 AA 2309 B8
DL 1342 UA 1652 C2
AA 2309 US 1901 C17
UA 1652
US 1901
US 1901

I want a formula/function that will look at table A, find AA 1234, then query table B, find AA 1234, then copy baggage claim B1 into the blank cell under column "baggage claim" in table A adjacent to every AA 1234 entry, and so on.....

Can anyone help me?

Hello,
I am Pete, and I'll be happy to assist with your question today.

 

This problem can be solved using a VLookup formula.

 

If you are not sure how to do it, or need further assistance, please attach the spreadsheet or a similar sample spreadsheet so I can insert the formula for you.

 

Customer: replied 3 years ago.

how do I attach the file?

Hi John,

You should see a paperclip icon in your reply box - clicking it starts a two-step process to attach a file.

 

Customer: replied 3 years ago.

it says it is for inserting an image.

It does say that, but it actually allows you to attach all sorts of files (up to 5MB in size) as well as images....

Customer: replied 3 years ago.
Customer: replied 3 years ago.
Yes, I got it and I have a solution for you now which I am trying to attach...
Hi John,

I have added the VLookup formula for you.
As the airline and flight were in separate columns, I first combined them in new columns (D & K) and then hid those new columns to make the data more presentable.

I am unable to attach the file here for some reason, so I have uploaded it to Wikisend and you can download it using the following link:

Pete and 2 other Mac Specialists are ready to help you
Customer: replied 3 years ago.

I forgot to tell you it has to factor in the arriving date. It appears vlookup does not. Look at AA 3009. It arrives on both the 14th and 15th, however; the baggage claim is different on the 15th than it is on the 14th. Your formula added the baggage claim for that flight on the 14th to the flight on BOTH the 14th and 15th. Can you fix it so that it looks at the arrival date, then the flight number?

Customer: replied 3 years ago.

disregard the date thing. I figured it out myself.