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.

=VLOOKUP(A1,INDIRECT("'Sheet2'!R1C"&MATCH(B1,Sheet2!$A$1:$F$1)&":R5C"&MATCH(B1,Sheet2!$A$1:$F$1)+1,FALSE),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.
>

Try this:

=VLOOKUP(A1,Sheet2!F$2:F$5:INDEX(Sheet2!A$2:F$5,,MATCH(B1,Sheet2!A$1:F$1,0)),2,0)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Rj" <(E-Mail Removed)> wrote in message
news:C0750B92-EBCF-43EA-BE88-(E-Mail Removed)...
>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.
>

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 C25 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" <(E-Mail Removed)> wrote in message
news:C0750B92-EBCF-43EA-BE88-(E-Mail Removed)...
> 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.
>