Can anyone help me figure out the easiest way to do this?

Sheet 1 Sheet 2

A B A B (Results)

1) 20 1) 70 1) 4050 1)
2) 30 2) 35 2) 6030 2)
3) 60 3) 10 3) 8010 3)
4) 50 4) 90 4) 2045 4)


Im trying to construct a lookup table using the above information as
example. This is what I want to do:

If the first two digits of Sheet 2 in Column A match any of the numbers in
range A1:A4 (Sheet 1) then I want the answer to be any word I choose €œCHARD€�
but if not in the first range then I want the formula to look at range B1:B4
(Sheet 1) and return €œMERLOT€�. Basically I want to use this guide line in
about 8 different ranges that will return a specific word for each range that
is met.

A big nested if statement like the following might work but if it's possible
can you just create two columns.....one with the 2 digit lookup value, and
the other with the return value? Like below? Then you can use the left(A1,2)
buried in a vlookup function to extract the 2 digits of interest from the
bigger string and create one simple IF statement.
A B
20 Chard
30 Chard
60 Chard
50 Chard
70 Merlot
35 Merlot
10 Merlot
90 Merlot

=IF(ISERROR(VLOOKUP(LEFT(I24,2),$E$24:$E$27,1,FALS E)),"Merlot","Chard")

"Santi" wrote:

Can anyone help me figure out the easiest way to do this?

Sheet 1 Sheet 2

A B A B (Results)

1) 20 1) 70 1) 4050 1)
2) 30 2) 35 2) 6030 2)
3) 60 3) 10 3) 8010 3)
4) 50 4) 90 4) 2045 4)


Im trying to construct a lookup table using the above information as
example. This is what I want to do:

If the first two digits of Sheet 2 in Column A match any of the numbers in
range A1:A4 (Sheet 1) then I want the answer to be any word I choose €œCHARD€�
but if not in the first range then I want the formula to look at range B1:B4
(Sheet 1) and return €œMERLOT€�. Basically I want to use this guide line in
about 8 different ranges that will return a specific word for each range that
is met.

Create a header row in row1
A1: CHARD
B1: MERLOT
so on....
Your data start in A2:H5

In Sheet 2

B1:
=IF(COUNTIF(Sheet1!$A$2:$H$5,LEFT(A1,2)),INDEX(She et1!$A$1:$H$1,MAX(INDEX((Sheet1!$A$2:$H$5=LEFT(A1, 2)+0)*COLUMN(Sheet1!$A$2:$H$5),))),"")

copy down


"Santi" wrote:

Can anyone help me figure out the easiest way to do this?

Sheet 1 Sheet 2

A B A B (Results)

1) 20 1) 70 1) 4050 1)
2) 30 2) 35 2) 6030 2)
3) 60 3) 10 3) 8010 3)
4) 50 4) 90 4) 2045 4)


Im trying to construct a lookup table using the above information as
example. This is what I want to do:

If the first two digits of Sheet 2 in Column A match any of the numbers in
range A1:A4 (Sheet 1) then I want the answer to be any word I choose €œCHARD€�
but if not in the first range then I want the formula to look at range B1:B4
(Sheet 1) and return €œMERLOT€�. Basically I want to use this guide line in
about 8 different ranges that will return a specific word for each range that
is met.