Looking up value in a string for a range

[Liam79]

Hello guys, I did have a look and search myself - but no luck on finding exactly what i need.

I was after a formula that would lookup the below, find the value, and then return the value in the adjacent column. Vlookup does not work for FALSE or TRUE. However if I click control F I am able to find the value in the range that way.

I have a value like this :

5511

A range in a column like this (ie with numbers and commas):



[TABLE="width: 314"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2840, 2842, 2846, 2847, 2848, 2849, 2850, 2851, 2857, 4160, 4161, 4164, 4165, 4167, 4168, 4169, 9507, 9531, 9557, 9589, 9599[/TD]
[/TR]
[TR]
[TD]4880, 4881, 4885, 4886, 4887, 4888, 4889, 5306, 5307, 5308, 5309, 9537, 9659, 9687, 9689[/TD]
[/TR]
[TR]
[TD]2220, 2221, 2222, 2227, 2228, 2229[/TD]
[/TR]
[TR]
[TD]1380, 1381, 1382, 1387, 1388, 1389, 5510, 5511, 5512, 5513, 5515, 5516, 5517, 5518, 5519, 5520, 5521, 5522, 5523, 5524, 5525, 5526, 5527, 5528, 5529, 5530, 5531, 5538, 5539[/TD]
[/TR]
[TR]
[TD]8314, 8315, 8316, 8318, 8319, 8364, 8365, 8366, 8368, 8369, 8374, 8375, 8376, 8377, 8378, 8390, 8391, 8392, 8393, 8394, 8395, 8396, 8397, 8398, 8399, 8400, 8403, 8404, 8405, 8406, 8407, 8408, 8409, 8716, 8717, 9400, 9401, 9402, 9409, 9410, 9413, 9414, 9415, 9418, 9428, 9429, 9430[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way I can use a formula to find my value in that column? In this instance the value is in row 9

 

[Aladin Akyurek]

What must be the result if 5511 is found?

 

[Liam79]

Hi Aladin, its to return a value in the next column.

I couldn't figure any formulas though that could find the value in the column to then be able to reference the column beside it.

The only way I could find it was Ctrl-F and finding it that way..

 

[Aladin Akyurek]

Would you please specify the ranges? In which column are we looking for 5511 and from which column are we returning a result?

 

[Liam79]

I have a picture, but dont have permission to post that.


I have a value like this :

5511 - assume this is the value in A1

A range in a column like this (ie with numbers and commas):

The below is column D, each line below represents what is in each line. In this instance the number im trying to find is in row 4 of Column D (ie 1380, 1381, 1382, 1387, 1388, 1389, 5510, 5511, 5512, 5513, 5515, 5516, 5517, 5518, 5519, 5520, 5521, 5522, 5523, 5524, 5525, 5526, 5527, 5528, 5529, 5530, 5531, 5538, 5539)

[TABLE="class: cms_table, width: 314"]
<tbody>[TR]
[TD]2840, 2842, 2846, 2847, 2848, 2849, 2850, 2851, 2857, 4160, 4161, 4164, 4165, 4167, 4168, 4169, 9507, 9531, 9557, 9589, 9599[/TD]
[/TR]
[TR]
[TD]4880, 4881, 4885, 4886, 4887, 4888, 4889, 5306, 5307, 5308, 5309, 9537, 9659, 9687, 9689[/TD]
[/TR]
[TR]
[TD]2220, 2221, 2222, 2227, 2228, 2229[/TD]
[/TR]
[TR]
[TD]1380, 1381, 1382, 1387, 1388, 1389, 5510, 5511, 5512, 5513, 5515, 5516, 5517, 5518, 5519, 5520, 5521, 5522, 5523, 5524, 5525, 5526, 5527, 5528, 5529, 5530, 5531, 5538, 5539[/TD]
[/TR]
[TR]
[TD]8314, 8315, 8316, 8318, 8319, 8364, 8365, 8366, 8368, 8369, 8374, 8375, 8376, 8377, 8378, 8390, 8391, 8392, 8393, 8394, 8395, 8396, 8397, 8398, 8399, 8400, 8403, 8404, 8405, 8406, 8407, 8408, 8409, 8716, 8717, 9400, 9401, 9402, 9409, 9410, 9413, 9414, 9415, 9418, 9428, 9429, 9430[/TD]
[/TR]
</tbody>[/TABLE]


And I want to return the value that is in cell E4 (I realise i said row 9 earleir, as it suited the example then. With the borders around it shows as row 4)

So, the number Im trying to find is within a cell that has several other numbers with in it, they are separated with columns.

I hope that makes sense..

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(","&A1&",",","&SUBSTITUTE($D$2:$D$100," ","")&","),

$E$2:$E$100)​

<strike></strike>
​

 

[Liam79]

That's correct. And after finding that 5511 we want to return the value in the cell to the right of that. Which in this instance show the value "MONDAY". Basically like a vlookup would do

 

[Aladin Akyurek]

That's correct. And after finding that 5511 we want to return the value in the cell to the right of that. Which in this instance show the value "MONDAY". Basically like a vlookup would do

See post #6 which I edited in order to post the solution you need..,.

 

[Liam79]

thanks very much Aladin

 

[Aladin Akyurek]

thanks very much Aladin

You are welcome.