Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup when lookup criteria is not an exact match
I realize the formula solution is probably not a vlookup function, but I'm
trying combinations of MATCH, SEARCH, ISNUMBER, INDEX, and still not getting the right result. I've got 2 worksheets. Worksheet 1, column C has a 15-digit ID like 02i40000001fpv0. Worksheet 2, column A has an 18-digit ID. The first 15 of the 18 are the same as above, but + an extra 3 numbers added at the end. I need to find the value in Worksheet 1 C2 in Worksheet column A, and return the 18 digit value in column A. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup when lookup criteria is not an exact match
You can use wildcards with VLOOKUP, so try this:
=VLOOKUP(Sheet1!C2&"*",Sheet2!A:A,1,0) Hope this helps. Pete On Nov 18, 5:01*pm, jtoy wrote: I realize the formula solution is probably not a vlookup function, but I'm trying combinations of MATCH, SEARCH, ISNUMBER, INDEX, and still not getting the right result. I've got 2 worksheets. Worksheet 1, column C has a 15-digit ID like 02i40000001fpv0. Worksheet 2, column A has an 18-digit ID. The first 15 of the 18 are the same as above, but + an extra 3 numbers added at the end. I need to find the value in Worksheet 1 C2 in Worksheet column A, and return the 18 digit value in column A. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup when lookup criteria is not an exact match
Try
=INDEX(Sheet2!A:A,MATCH(C1&"*",Sheet2!A:A,0)) 'error handled =IF(ISNA(MATCH(C1&"*",Sheet2!A:A,0)),"", INDEX(Sheet2!A:A,MATCH(C1&"*",Sheet2!A:A,0))) If this post helps click Yes --------------- Jacob Skaria "jtoy" wrote: I realize the formula solution is probably not a vlookup function, but I'm trying combinations of MATCH, SEARCH, ISNUMBER, INDEX, and still not getting the right result. I've got 2 worksheets. Worksheet 1, column C has a 15-digit ID like 02i40000001fpv0. Worksheet 2, column A has an 18-digit ID. The first 15 of the 18 are the same as above, but + an extra 3 numbers added at the end. I need to find the value in Worksheet 1 C2 in Worksheet column A, and return the 18 digit value in column A. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DSUM Criteria - find exact match only | Excel Worksheet Functions | |||
Vlookup- Closest match that contains the exact lookup value? | Excel Worksheet Functions | |||
Lookup Exact Match | Excel Worksheet Functions | |||
Lookup and Match with not exact numbers | Excel Worksheet Functions | |||
Match - Exact - Lookup? | Excel Worksheet Functions |