VLOOK UP to match the cell contains separated by comma

[sainathd]

Hi,

I have table consisting of two columns shown below, I need to match say R38 which is col 1 row 1,I want it to return 0.959901738 which is col 2, how can I do it?
Using wild card I cannot match the entire R38, I will have to write only R3 which may have another value assigned elsewhere hence returning the value that I don't desire.

Awaiting response
[TABLE="width: 613"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 518"]
<tbody>[TR]
[TD="class: xl22021, width: 518"]R5-R9,R15,R19,R28-R31,R38,R39,R47,R151[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl22021, width: 95"]0.959901738[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 518"]
<tbody>[TR]
[TD="class: xl22021, width: 518"]R217,R294,R406,R407,R432-R443,R484[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0.66[/TD]
[/TR]
[TR]
[TD]R44[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

With your lookup value A4, change to match your data

=VLOOKUP("*"&A4&"*",A1:B2,2,0)

 

[sainathd]

Hi,
Appreciate your response however when I tried your formula, it always returned the first cell value of column 2 instead of it's corresponding column and also for lookup value A4, I cannot select a cell say D4,I need to drag the formula for nearly 500 lines, I cannot type the lookup value for all.Please let me know if you even better solution

 

[Scott T]

Not sure exactly what you want. Do you want to return 0.959901738 or the column number 2? How do you want to input the lookup value? Well it change?

 

[Aladin Akyurek]

E1: R38

In F1 enter and copy down:

=IF($E1="","",IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(","&SUBSTITUTE($E1," ","")&",",","&SUBSTITUTE($A$1:$A$10," ","")&","),$B$1:$B$10),"not found"))

 

[sainathd]

I want to return the data in column number 2, I'd like to ask you that instead of lookup value A4 in your formula,Is there any way to input cell number, because it would make it easier for me when I'll copy it for all other cells.

 

[sainathd]

Not working, I get blank output

E1: R38

In F1 enter and copy down:

=IF($E1="","",IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(","&SUBSTITUTE($E1," ","")&",",","&SUBSTITUTE($A$1:$A$10," ","")&","),$B$1:$B$10),"not found"))

 

[Aladin Akyurek]

Not working, I get blank output

Interesting, but see: Dropbox - sainathd.xlsx

 

[sainathd]

could you email me @ sainathd@hcl.com, I cannot access Dropbox

Interesting, but see: Dropbox - sainathd.xlsx

 

[Scott T]

I want to return the data in column number 2, I'd like to ask you that instead of lookup value A4 in your formula,Is there any way to input cell number, because it would make it easier for me when I'll copy it for all other cells.

Not sure what you mean by input cell number.