Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
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)) -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
Thank you so much! Only tried the one I thought looked the best (your 2nd
option) and it worked great. Thanks again, Les "T. Valko" wrote: 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)) -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
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 "T. Valko" wrote: 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)) -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
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" wrote: 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)) -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"LOOKUP" question
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? -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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" wrote: 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)) -- Biff Microsoft Excel MVP "WLMPilot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) | |||
Lookup using 2 cells as the "X" and "Y" coordinates.... | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |