index and matching with string search!

[via135]

hi

in sheet1 i'm having 2 col thru a1:b6

VALUE CODE
100 DDP 111
50 DDP 222
200 DD 666
500 DT 030208 DDP 444
600 DP 555

in sheet2 two col thru a1:b10

CODE VALUE
111 100
222 50
444 500
333 200
555 600
666 200
777 100
888 200
999 400

how can i index col B (value) by matching col A (code) in sheet2 with
col B (code) in sheet1?


help pl!

-via135

 

[pdberger]

VIA --

Obviously, it would be easiest to create one lookup table. Given that you
can't do that, you can create nested VLOOKUP statements:

=VLOOKUP(VLOOKUP(D20,'Sheet1'!A1:B9,2,FALSE),'Sheet2'!A1:B5,2,FALSE)

The 'inside' vlookup creates the lookup value for the 'outside' vlookup.

Worked for me.

HTH

 

[T. Valko]

Try this:

Enter this formula on Sheet2 B2 and copy down as needed:

=LOOKUP(2,1/FIND(A2,Sheet1!B$2:B$6),Sheet1!A$2:A$6)

Note that based on your sample data 333, 777, 888 and 999 all return #N/A.

 

[via135]

Try this:

Enter this formula on Sheet2 B2 and copy down as needed:

=LOOKUP(2,1/FIND(A2,Sheet1!B$2:B$6),Sheet1!A$2:A$6)

Note that based on your sample data 333, 777, 888 and 999 all return #N/A.

--
Biff
Microsoft Excel MVP













- Show quoted text -

thks Biff...this is what i exactly want.!
thks again.!

-via135

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

Enter this formula on Sheet2 B2 and copy down as needed:

=LOOKUP(2,1/FIND(A2,Sheet1!B$2:B$6),Sheet1!A$2:A$6)

Note that based on your sample data 333, 777, 888 and 999 all return #N/A.

--
Biff
Microsoft Excel MVP













- Show quoted text -

thks Biff...this is what i exactly want.!
thks again.!

-via135