index and matching with string search!
 

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

index and matching with string search!
 

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),'Shee t2'!A1:B5,2,FALSE)

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

Worked for me.

HTH


"via135" wrote:

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

index and matching with string search!
 

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


"via135" wrote in message
...
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

index and matching with string search!
 

On Apr 16, 8:31*am, "T. Valko" wrote:
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

"via135" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

-via135

index and matching with string search!
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"via135" wrote in message
...
On Apr 16, 8:31 am, "T. Valko" wrote:
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

"via135" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

-via135