Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lookup from a table
I am trying to do a lookup that looks like this.
SHEET1 A B C 1 7000 AAA Need Formula to = 'RED' 2 8000 BBB Need Formula to = 'RED' 3 7000 BBB Need formula to = 'BLUE' 4 6000 CCC Need formula to = 'GREEN' 5 7000 CCC Need formula to = 'WHITE' SHEET 2 A B C D E F 1 AAA BBB CCC 2 5000 GREEN 4500 WHITE 4100 YELLOW 3 6000 WHITE 5500 YELLOW 6000 GREEN 4 7000 RED 7000 BLUE 7000 WHITE 5 8000 YELLOW 8000 RED 8500 RED I am trying to write a formula for C1:C5 in SHEET 1 to automatically find the colors using the ARRAY A1:F5 of SHEET 2. For instance, 7000 under AAA = Red, but 7000 under BBB = BLUE. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lookup from a table
=VLOOKUP(A1,INDIRECT("'Sheet2'!R1C"&MATCH(B1,Sheet 2!$A$1:$F$1)&":R5C"&MATCH(B1,Sheet2!$A$1:$F$1)+1,F ALSE),2,FALSE)
If your array is actually larger, change the number in "R5C" to whatever row it needs to be. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Rj" wrote: I am trying to do a lookup that looks like this. SHEET1 A B C 1 7000 AAA Need Formula to = 'RED' 2 8000 BBB Need Formula to = 'RED' 3 7000 BBB Need formula to = 'BLUE' 4 6000 CCC Need formula to = 'GREEN' 5 7000 CCC Need formula to = 'WHITE' SHEET 2 A B C D E F 1 AAA BBB CCC 2 5000 GREEN 4500 WHITE 4100 YELLOW 3 6000 WHITE 5500 YELLOW 6000 GREEN 4 7000 RED 7000 BLUE 7000 WHITE 5 8000 YELLOW 8000 RED 8500 RED I am trying to write a formula for C1:C5 in SHEET 1 to automatically find the colors using the ARRAY A1:F5 of SHEET 2. For instance, 7000 under AAA = Red, but 7000 under BBB = BLUE. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lookup from a table
Try this:
=VLOOKUP(A1,Sheet2!F$2:F$5:INDEX(Sheet2!A$2:F$5,,M ATCH(B1,Sheet2!A$1:F$1,0)),2,0) Copy down as needed. -- Biff Microsoft Excel MVP "Rj" wrote in message ... I am trying to do a lookup that looks like this. SHEET1 A B C 1 7000 AAA Need Formula to = 'RED' 2 8000 BBB Need Formula to = 'RED' 3 7000 BBB Need formula to = 'BLUE' 4 6000 CCC Need formula to = 'GREEN' 5 7000 CCC Need formula to = 'WHITE' SHEET 2 A B C D E F 1 AAA BBB CCC 2 5000 GREEN 4500 WHITE 4100 YELLOW 3 6000 WHITE 5500 YELLOW 6000 GREEN 4 7000 RED 7000 BLUE 7000 WHITE 5 8000 YELLOW 8000 RED 8500 RED I am trying to write a formula for C1:C5 in SHEET 1 to automatically find the colors using the ARRAY A1:F5 of SHEET 2. For instance, 7000 under AAA = Red, but 7000 under BBB = BLUE. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I lookup from a table
Hi,
You can also try the following: 1. On sheet 2, highlight A2:B5 and press Ctrl+F3 - assign it a name, say AAA. Please do the same for C2:D5 and E2:F5 as well. Assign BBB and CCC; 2. Now in cell C1 of sheet 1, you can use the following VLOOKUP() formula =vlookup(A1,indirect(B1),2,0) Please ensure that the names in column B of sheet1 are the same as the names defined on sheet 2. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rj" wrote in message ... I am trying to do a lookup that looks like this. SHEET1 A B C 1 7000 AAA Need Formula to = 'RED' 2 8000 BBB Need Formula to = 'RED' 3 7000 BBB Need formula to = 'BLUE' 4 6000 CCC Need formula to = 'GREEN' 5 7000 CCC Need formula to = 'WHITE' SHEET 2 A B C D E F 1 AAA BBB CCC 2 5000 GREEN 4500 WHITE 4100 YELLOW 3 6000 WHITE 5500 YELLOW 6000 GREEN 4 7000 RED 7000 BLUE 7000 WHITE 5 8000 YELLOW 8000 RED 8500 RED I am trying to write a formula for C1:C5 in SHEET 1 to automatically find the colors using the ARRAY A1:F5 of SHEET 2. For instance, 7000 under AAA = Red, but 7000 under BBB = BLUE. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Lookup table | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
lookup a value on a table | Excel Worksheet Functions | |||
Table Lookup | Excel Worksheet Functions |