Vlookup string

I am trying to use vlookup to lookup numbers in a column which I have working if there is only one number (1230.002) in the cell but I have some cells with multiple numbers seperated by a comma (1345.000, 1423.002, 7634.003). I would like it to return the value if it matches any of the numbers in the cell. The cells contain from one to four numbers. Any help would be appreciated. Thanks

Well, you won't be able to do that with a simple VLOOKUP but assuming the following:

If there are multiple matches then, like a VLOOKUP, just the first should be returned

You have your lookup value (with possibly multiple numbers) in A1
You want to search in C2:C10 and return the corresponding value from D2:D10

Try this formula

=INDEX(D2:D10,MIN(IF(ISNUMBER(MATCH("*"&C2:C10&",*",A1&",",0)),ROW(C2:C10)-ROW(C2)+1)))

confirmed with CTRL+SHIFT+ENTER

I think this might clarify as I am a little confused:

tabel 1 (Column A has the multiple numbers in the cells)
A b c


Table 2 (Column A has the single numbers)
A b c


I want to lookup the number from Table2 column A and match Table 1 column A and return the the value from Table 1 column b to Table 2 column b. Thanks

OK, I think I got it the wrong way round. From what you say you CAN do this with a modified VLOOKUP.

Assuming your lookup value is in A1 and you want to lookup in A8:A20 and return a value from B8:B20. You could use this formula

=VLOOKUP("*"&A1&"*",A8:B8,2,0)

however it may be a little risky because it will find any partial match, e.g. if A1 contains 42 it will find a match with 1345.000, 1423.002, 7634.003 because 42 is contained within that string.

To avoid that you could utilise the commas so that a match will only be found if it's a complete number like 1345.000. Note: this formula assumes that all numbers except the last in each cell will be followed by a comma

=INDEX(B8:B20,MATCH("*"&A1&",*",A8:A20&",",0))

confirmed with CTRL+SHIFT+ENTER

I think we are close but I get #value! error. Here is my formula.
=INDEX(Sheet1!B1:B298,MATCH("*"&A1&",*",Sheet1!A1:A298&",",0))
Both A columns are formated as numbers. Any ideas/Thanks

[deleted]

EDIT... TAKE that back.. I misread the formula I think

Originally Posted by jeffsal

I think we are close but I get #value! error. Here is my formula.
=INDEX(Sheet1!B1:B298,MATCH("*"&A1&",*",Sheet1!A1:A298&",",0))
Both A columns are formated as numbers. Any ideas/Thanks

You need to confirm with CTRL+SHIFT+ENTER

select the cell with the formula
press F2
hold down CTRL and SHIFT keys and at the same time press ENTER
curly braces like { and } should appear around the formula in the formula bar and the formula should work [hopefully]