PDA

View Full Version : VLOOKUP, INDEX and MATCH formatting problem



trav
03-15-2017, 10:36 AM
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.

mdmackillop
03-15-2017, 11:10 AM
Data in the Concept column must appear exactly in Column A of AcctList

i.e. "Hangar Rent" not "Hangar Rent for April"

trav
03-15-2017, 12:12 PM
It seems odd that such a powerful program cannot handle such a basic task through any of it many formulas. Isn't there any other that will solve the issue?

mdmackillop
03-15-2017, 01:21 PM
Here's a solution of sorts which searches for your AccList terms. It could not be considered reliable if there is so much uncertainty in the Concept field. The workbooks should be designed properly.

trav
03-15-2017, 02:04 PM
That is a clever solution but, as you indicate, it does not seem to be entirely reliable.
If nothing else, it makes feel better about the many hours I spent trying to solve the problem myself and couldn't.
Perhaps a database will be better suited for my needs.
Thank you for your assistance.

rlv
03-15-2017, 07:30 PM
Vlookup is one of excel's most powerful functions for referencing information in a table, but it is not magic. It is not reasonable, for example, to feed it a long sentence like "Aircraft Crew Services: Monthly Estimated Payroll Costs: emp1" and expect it to somehow know which of those words is imporant to you. You have to help it by writing some vba code. See attached example of one approach.