"LOOKUP" question

[WLMPilot]

I need help in determining the formula to lookup a value. I am using two
worksheets I will call A & B.

On A, I enter a 3-letter airport identifier, ie AND (Anderson). Using AND
as the reference, I want to fill in other information on sheet A from sheet
B.

On Sheet B, I have the information pertained to various airports with the
row headers being in A:A.
A1 = Airport Identifier C1 = AND
A2 = UNICOM C2 = 122.8
A3:A20 (other row headers) C3:C20 (info pertaining to airport AND)

Other airports will be entered on Sheet B to be referenced in subsequent
columns.

In Sheet A (for example) I have the following:
A1 = "Airport Identifier" B1 --> I enter AND
A2 = "UNICOM " B2 --> Pulls info from Sheet B based on B1

My question is how do I get it to find the UNICOM freq in Sheet B? Is it
just going to search via AND and I have to tell it the row (as I understand
HLOOKUP), or can I use both AND and UNICOM to pinpoint the value and place in
cell B2 on Sheet A?

Thanks,
Les

 

[T. Valko]

HLOOKUP is the best choice. Here are some options:

=HLOOKUP(B1,B!C1:F20,2,0)

=HLOOKUP(B1,B!C1:F20,MATCH(A2,B!A1:A20,0),0)

=VLOOKUP(A2,B!A1:F20,MATCH(B1,B!A1:F1,0),0)

=INDEX(B!C2:F20,MATCH(A2,B!A2:A20,0),MATCH(B1,B!C1:F1,0))

 

[WLMPilot]

Thank you so much! Only tried the one I thought looked the best (your 2nd
option) and it worked great.

Thanks again,
Les

 

[WLMPilot]

One more question: I am getting a #N/A if there is no value at the point of
lookup, ie not every airport has a control tower so I do not enter anything.
What do I need to do to keep the cell "empty" if there is not a value?

Thanks,
Les

 

[WLMPilot]

I just came across another problem. In HLOOKUP, there is always a
lookup_value. Example: I may enter the airport identifier (AND) and need
the city airport is located in but I do not have a lookup value to go by. Of
course, I can manually enter a lookup value.

Is there a way without me having to enter the lookup value?

Les

 

[T. Valko]

For the #N/A problem:

This will leave the cell blank.

=IF(ISNA(HLOOKUP(B1,B!C1:F20,MATCH(A2,B!A1:A20,0),0)),"",HLOOKUP(B1,B!C1:F20,MATCH(A2,B!A1:A20,0),0))

For the other problem:

Where is the airport city data located?