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):

2840, 2842, 2846, 2847, 2848, 2849, 2850, 2851, 2857, 4160, 4161, 4164, 4165, 4167, 4168, 4169, 9507, 9531, 9557, 9589, 9599

4880, 4881, 4885, 4886, 4887, 4888, 4889, 5306, 5307, 5308, 5309, 9537, 9659, 9687, 9689

2220, 2221, 2222, 2227, 2228, 2229

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

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

<colgroup><col></colgroup><tbody>
</tbody>

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)

2840, 2842, 2846, 2847, 2848, 2849, 2850, 2851, 2857, 4160, 4161, 4164, 4165, 4167, 4168, 4169, 9507, 9531, 9557, 9589, 9599

4880, 4881, 4885, 4886, 4887, 4888, 4889, 5306, 5307, 5308, 5309, 9537, 9659, 9687, 9689

2220, 2221, 2222, 2227, 2228, 2229

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

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

<tbody>
</tbody>

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.