Search a list, return value based on partial text

[edam4i]

Hi would appreciate any help here please.

• I want to paste in financial transactions into Col A
• Have them automatically categories in Col B
• Based on a list in Col H
• The ist will have partial text

Hope I explained sufficiently, if not please let me know.
Thanks,
C.

 

[Z51]

Not completely sure this is what you're after, but try this in B3 filled down. Adjust your ranges as needed.

=INDEX($G$3:$G$7,LOOKUP(9E+307,SEARCH($H$3:$H$7,A3)))

 

[edam4i]

Not completely sure this is what you're after, but try this in B3 filled down. Adjust your ranges as needed.

=INDEX($G$3:$G$7,LOOKUP(9E+307,SEARCH($H$3:$H$7,A3)))

Thanks, could you tell me what the meaning of 9E+307 is please so I can better understand the code.
Thanks,

 

[Z51]

After looking at the results while trying to write up an explanation, my formula does not work properly. Amazingly, it only works based on the order of your sample data. If you re-arrange the values in column A, it stops working.

ALDI and LIDL return GROCERIES because the LOOKUP function is returning a 1 - but that '1' is from the position of the partial match in the string, not the position of the array returned by the SEARCH function. "ALD" and "LID" are found starting in position 1 of "ALDI" and "LIDL", respectively.

Same applies to VODAFONE ("OFA" is found in position 3, hence the third value, INTERNET, is returned. For NETFLIX, "FLIX" is in position 4 and for STARBUCKS, "BUCKS" is in position 5. So in each example it "successfully" match the correct row. Now I've seen it all, haha.

So some work still needs to be done with this one, but back to your original question about 9E+307...

9E+307 is a very, very large number (exponential notation, equivalent to 9 followed by 307 zeros) - greater than any number anyone is likely to enter into a cell. If LOOKUP doesn't find an exact match to that number it will return the next largest number it finds in the range/array. See the Excel help files for its usage, limitations and more details about how the function works.

 

[edam4i]

After looking at the results while trying to write up an explanation, my formula does not work properly. Amazingly, it only works based on the order of your sample data. If you re-arrange the values in column A, it stops working.

ALDI and LIDL return GROCERIES because the LOOKUP function is returning a 1 - but that '1' is from the position of the partial match in the string, not the position of the array returned by the SEARCH function. "ALD" and "LID" are found starting in position 1 of "ALDI" and "LIDL", respectively.

Same applies to VODAFONE ("OFA" is found in position 3, hence the third value, INTERNET, is returned. For NETFLIX, "FLIX" is in position 4 and for STARBUCKS, "BUCKS" is in position 5. So in each example it "successfully" match the correct row. Now I've seen it all, haha.

So some work still needs to be done with this one, but back to your original question about 9E+307...

9E+307 is a very, very large number (exponential notation, equivalent to 9 followed by 307 zeros) - greater than any number anyone is likely to enter into a cell. If LOOKUP doesn't find an exact match to that number it will return the next largest number it finds in the range/array. See the Excel help files for its usage, limitations and more details about how the function works.

Ok thank you for your effort and explanation too. If you can develop this that would be great if you can't please just let me know and I'l try somewhere else. Thanks again!

 

[Z51]

This formula may work better (hopefully). Give it a shot.

=INDEX($G$3:$G$7,MATCH(TRUE,ISNUMBER(SEARCH($H$3:$H$7,A3)),0))

 

[edam4i]

This formula may work better (hopefully). Give it a shot.

=INDEX($G$3:$G$7,MATCH(TRUE,ISNUMBER(SEARCH($H$3:$H$7,A3)),0))

thanks so much this worked a treat for me! really appreciate your help on this.