vlookup type function, see if string has key, if it does give value

So i need to do a vlookup type function, where i need to search for a key in an array, if i find it, spit out the value.

For the array i'm using a table on sheet 2.

On sheet one Col A is the string I want to search, col B is where I would want the category to show.

Sheet two has col A with the Key (Merchants) and I want to show the Category which is Col B

See attached Workbook.

I used a VLOOKUP function combined with an IF statement to yield "NO CATEGORY" when an item isn't in the list.

Formula fragments follow from Sheet 1, cell A2:

Please Login or Register  to view this content.

HINT: If you want to expand the LOOKUP TABLE, DO NOT add another item at the end.
Insert a line inside the LOOKUP TABLE boundaries, and the formulas will automatically
adjust themselves. If you need to add a LAST ITEM, insert a row before the last item,
'cut and paste' the last row to the newly inserted row, and then add the text on the
LAST LINE.

Originally Posted by LJMetzger

See attached Workbook.

I used a VLOOKUP function combined with an IF statement to yield "NO CATEGORY" when an item isn't in the list.

Formula fragments follow from Sheet 1, cell A2:

Please Login or Register  to view this content.

HINT: If you want to expand the LOOKUP TABLE, DO NOT add another item at the end.
Insert a line inside the LOOKUP TABLE boundaries, and the formulas will automatically
adjust themselves. If you need to add a LAST ITEM, insert a row before the last item,
'cut and paste' the last row to the newly inserted row, and then add the text on the
LAST LINE.

Thanks LJ that's what I had before and doesn't work. It doesn't search substrings.

Originally Posted by benishiryo

welcome to the forum, edsters. is this what you need?
=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$15,A1),Sheet2!$B$1:$B$14),"")

Thanks Benishiryo that works perfectly, by anychance can you explain the 2^15 part of your formula. I wish to get a better understanding of the formula for future use.

That was my understanding as well LJMetzger however as I am creating a sheet for my finances, I couldn't have them categorized incorrectly.

Your help however was greatly appreciated!