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]

 

[Aladin Akyurek]

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

Would you try the dropbox link once more?

By the way, what is the return that you expect for R435?

 

[sainathd]

[TABLE="width: 964"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]R226,R227[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD]lookup value[/TD]
[TD]Expected result[/TD]
[/TR]
[TR]
[TD]R41,R42,R549[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R577,R625[/TD]
[TD]3.199672462[/TD]
[TD] [/TD]
[TD]R549[/TD]
[TD]0.9599017[/TD]
[/TR]
[TR]
[TD]R531[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R552[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD]R625[/TD]
[TD]3.1996725[/TD]
[/TR]
[TR]
[TD]R551[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R530[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R570[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R498[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R13[/TD]
[TD]3.199672462[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]R274[/TD]
[TD]0.959901738[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
By Cell name I mean D9/C4/A4/E10 ... I think it's called cell number, not sure...

Another example when using VLOOKUP(D9,D1:E100,2,FALSE) where D9(lookup value) is cell name/number

Not sure what you mean by input cell number.

 

[sainathd]

Would you try the dropbox link once more?

By the way, what is the return that you expect for R435?

it's adjacent value

[TABLE="class: cms_table, width: 964"]
<tbody>[TR]
[/TR]
[TR]
[TD]R226,R227[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD]lookup value[/TD]
[TD]Expected result[/TD]
[/TR]
[TR]
[TD]R41,R42,R549[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R577,R625[/TD]
[TD]3.199672462[/TD]
[TD][/TD]
[TD]R549[/TD]
[TD]0.9599017[/TD]
[/TR]
[TR]
[TD]R531[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R552[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD]R625[/TD]
[TD]3.1996725[/TD]
[/TR]
[TR]
[TD]R551[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R530[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R570[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R498[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R13[/TD]
[TD]3.199672462[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R274[/TD]
[TD]0.959901738[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

it's adjacent value
[...]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] R226,R227[/td][td] 0.959901738[/td][td] [/td][td] lookup value[/td][td] Expected result[/td][/tr]
[tr][td]

2​

[/td][td] R41,R42,R549[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] R577,R625[/td][td] 3.199672462[/td][td] [/td][td] R549[/td][td] 0.9599017[/td][/tr]
[tr][td]

4​

[/td][td] R531[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td] R552[/td][td] 0.959901738[/td][td] [/td][td] R625[/td][td] 3.1996725[/td][/tr]
[tr][td]

6​

[/td][td] R551[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

7​

[/td][td] R530[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

8​

[/td][td] R570[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

9​

[/td][td] R498[/td][td] 0.959901738[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

10​

[/td][td] R13[/td][td] 3.199672462[/td][td] [/td][td][/td][td][/td][/tr]
[/table]

In E3 enter and copy down:

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