VLOOKUP, INDEX and MATCH Formatting Issue

[trav1]

I am unable to resolve the VLOOKUP text problem on the attached spreadsheet.
Rows 3 and 4 on Sheet1 are unable to find the text on the "Account List" tab.
Column D on "Account List" has been pasted from another source.
Row 6 of Sheet 1 shows what the formula is looking for but I can't tell why it can't find it on D4.
I have also tried with INDEX MATCH and I seem to run into the same problem.
Any help will be greatly appreciated.

[loginjmor]

Hi -

The bulk of your problem is the things you are having VLOOKUP search for don't exist on your list (AcctList). For example, the formula in Cell A3 is searching for "Hangar Rent for April 2016". If you go to the next tab, there is no "Hangar Rent for April 2016" in that list. So, your search strings have to match your keywords. And, since you specified FALSE in your VLOOKUP, it has to be an EXACT match, letter for letter - the whole thing. If you change that to TRUE, VLOOKUP will bring back any approximate matches, but that's far from foolproof.

Hope this helps.

[trav1]

Thanks for the prompt response. Isn't there another formula that can handle the issue?

[FDibbins]

There is no formula that will read your mind and know that, this time, you want to only use the 1st 2 words to search on, but next time, you want to search on some random work inside a string of other words, and the next time you want to search for a word than sometimes ends with a colon, and sometimes doesnt.

I suggest that you clean up your search criteria, and maybe also your criteria list

[loginjmor]

Hi -

Are you trying to find keywords in your comment strings and then associate an account to that comment?

[trav1]

That is precisely what I am trying to do

[loginjmor]

OK - Let's try this. In cell A3, copy and paste this formula:

=INDEX('Account List'!$B$2:$B$13,MATCH(MIN(IFERROR(SEARCH('Account List'!$A$2:$A$13,Sheet1!D3),LEN(Sheet1!D3)+1)), SEARCH('Account List'!$A$2:$A$13,Sheet1!D3)))

This is an Array Formula. Meaning we are feeding functions that normally only accept one cell as an argument a whole array (or list) of cells. So from the Formula editing window, you will have to press Ctrl-Shift-Enter to make Excel engage the Array formula functionality. If you did it correctly, Excel will add curly braces {} around the formula.

What this does is the SEARCH function looks at each item in your search list and searches the string in cell D3 for any matches. If there's no match, it will return a number larger than the length of the string in D3 (which is clearly not a match). Then we use the MIN function to search the Array that SEARCH created (one entry for every keyword) to find the smallest value (which means there is a match). The position of that smallest value corresponds to the row number that the matching keyword is in, so MATCH returns that number, and then it's as simple as using INDEX to go to the proper row and return the Expense Type.

Copy this formula into Cell B3:

=INDEX('Account List'!$C$2:$C$13,MATCH(MIN(IFERROR(SEARCH('Account List'!$A$2:$A$13,Sheet1!D3),LEN(Sheet1!D3)+1)), SEARCH('Account List'!$A$2:$A$13,Sheet1!D3)))

And this one into D3:

=INDEX('Account List'!$D$2:$D$13,MATCH(MIN(IFERROR(SEARCH('Account List'!$A$2:$A$13,Sheet1!D3),LEN(Sheet1!D3)+1)), SEARCH('Account List'!$A$2:$A$13,Sheet1!D3)))

Do the Ctrl-Shift-Enter on both of them and then copy down.

Hope this helps.

[trav1]

It took me a while to respond because I am in a Mac environment and finding the right key combination to engage the Array Formula has taken some Googling. For those who may benefit form this post in the future, what has worked for me is F2 + Ctrl + Shift + Return. But different types of Macs may use a different key combination (https://answers.microsoft.com/en-us/...d-d4a30116e9a3)

Your formulae work very well using the last formula on C3 instead of D3. The only instance where I have had a problem is when I input "In House Computerized Pilot Training Program" but when I used "Training: CAE Pilot Recurrent." I had no problem.

No doubt there is a long list of people who are indebted to you. Please add my name to it.

Thank you!

[trav1]

Please disregard comment about "In House Computerized Pilot Training Program". I can see the reason for the error.
Thanks again.

[loginjmor]

Great! Glad I could help. Please don't forget to mark this thread as SOLVED per the instructions at the bottom of this post.

[trav1]

Marking post SOLVED now. I have sent you a private message.