search string and return adjacent value.

Question

I am using the following array formula to search a range (HTS_COMBO) for a number and return the adjacent cell in another range (item_nums). Column Q contains just a numerical list (1,2,3,4,5,6...) starting at row 8.

(array formula)

=INDEX(ITEM_NUMS,SMALL(IF(HTS_COMBO=J$4,ROW(HTS_COMBO)-MIN(ROW(HTS_COMBO))+1),Q8))

My lookup range (hts_combo) has a series of numbers. Some of the numbers have two sets of numbers in one cell. If, J$4 contains 1806900073, then currently it doesn't recognize it. I need to it search the entire string for a match regardless of the place. 

Sample of how HTS_Combo range looks.

Answer 1

Hi,

Try this array formula

=INDEX(ITEM_NUMS,SMALL(IF(ISNUMBER(SEARCH(J$4,HTS_COMBO)),ROW(HTS_COMBO)-MIN(ROW(HTS_COMBO))+1),Q8))

Answer 2

Thanks!

This works....

When I make my first million..... I'm buying you a Krystal WITH cheese!

(...or White Castle if you prefer)

Answer 3

Thanks. But this would only pull the left number (first 10 digits). I needed it to look up both if there was more than one in the string.

Answer 4

How about

=INDEX(ITEM_NUMS,SMALL(IF(HTS_COMBO=left(J$4,10),ROW(HTS_COMBO)-MIN(ROW(HTS_COMBO))+1),Q8))

best wishes