Search cell for words in table and then return table header

Hi Leo,

See if this will do you for a while. I reformatted your lookup list and then did an Index match search. I also created two Dynamic Named Ranges to make it work if the lists grow.

NOTE - this only works if you sort by the first column.

Charge to Category Index Match Alpha List.xlsx

I'm ideally looking for a formula that works in place without changing the Payees page.

I need the Payees page to remain as it is.

Hi leo,

I keep running into questions that are just too hard (like your problem). Using VBA or "Fuzzy Logic" your problem is still hard. I've done a similar problem for myself using a long list of credit card transactions. I would then create an Advanced filter with words, but put "*" in front and behind each word. This then does a wildcard search and would find what you want in your post. Instead you want a formula that looks into one text string for another. In your Tesco example, the match isn't exact, so wildcard search is the best. But then you want two ranges to search between each other. Now the problem just gets too hard for a simple Excel formula.

Read up on a problem like yours at:
http://www.excel-university.com/perf...okup-in-excel/
Maybe they've solved it.

Edit: It looks like the Fuzzy Add-In would do your problem, but then I don't think it works on a Mac.

Another way.

Array enter this formula in H1 and fill down.

Formula:

Please Login or Register  to view this content.

The array constant {1,1,1} can be determined by additional calculation in case number of rows is not constant. Let me know.

Dave. That's amazing. I wish I fully understood it.

The Payees list will change number of rows over time. On my actual workbook it's already at 200.

I think I'll need some help with that additional calculation you mentioned.

What's the best way to count longest column in the array and return {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,..... to the length of the count?

Thanks for everything so far.

Originally Posted by leovfx

Is this bad practice?:

Please Login or Register  to view this content.

No. When the number of rows is small and unchanging it's more efficient. It's just gets awkward when the number of rows in the source range become large ... like 200 ... or change frequently.

Swap {1,1,1} for TRANSPOSE(ROW(INDIRECT("1:"&ROWS(PAYEES!$A$2:$K$100))))^0 That will return how ever many 1s = rows in PAYEES!$A$2:$K$100.

Then consider naming the range PAYEES!$A$2:$K$100 and then replacing the range reference(s) with that name. That way you just have to edit the range in Name Manager once rather than multiple times in the formula.

PS. Another alternative would be COLUMN(INDEX($1:$1,1):INDEX($1:$1,ROWS(PAYEES!$A$2:$K$100)))^0. It's longer but in case the volatile INDIRECT starts to slow the workbook down this could be a fall back.

You're welcome. Thanks for the feedback.

Hi,

One last thing to make this perfect...

Currently the search appears to be case sensitive. Is there a simple way to change it so that it isn't?

Can I just change FIND to SEARCH or is there a special reason that you used FIND that I'm overlooking?

Thanks for the back-up John.

_______________