Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
string matching | Excel Worksheet Functions | |||
search if a string contains text matching items in a list | Excel Worksheet Functions | |||
index and matching in userform! | Excel Discussion (Misc queries) | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Index & Matching Functions | Excel Discussion (Misc queries) |