VLOOKUP and INDEX

[ajocius]

My system is using XP Pro with MS Excel 2007. I have spreadsheet I'm trying to use as a status sheet. See attached spreadsheet. There is a format control that has my projects listed. Once a project is selected my expectation is to have all (currently most) cells to auto populate. There is a project called Rehab Kirkuk Museum-CERP and when I select this everything populates exactly as I wanted it to. Next I select another project, like the first and all is fine. But when I select another different project than the Rehab Kirkuk Musem project nothing happens. Selecting a different project each time results in nothing. Only when I return to the Rehab Kirkuk Museum-CERP does everything populate again. In the next selection is fine as well, but after that nothing works correctly. What is wrong with my setup? I suspect my Index and Vlookup functions are somehow having difficulty with my data. How do I fix this? See attached spreadsheet.

Tony

[DonkeyOte]

You're using VLOOKUP whereas I suspect you want to use INDEX/MATCH where INDEX determines column to retrieve and MATCH the row...

VLOOKUP can only look left to right, presently the below:

=VLOOKUP($C$11,'ISOC-NK'!$A$2:$DN$31,1,TRUE)

is looking up say:

CERP Renovate Kirkuk Public Library

against Column A on ISOC-NK sheet whereas this value actually resides in Col O on that sheet so perhaps you should be using:

=INDEX('ISOC-NK!$A:$A,MATCH($C$11,'ISOC-NK!'$O:$O,0))

The same logic can be applied elsewhere by modifying the INDEX range to be the appropriate column to be returned.

NOTE: given the MATCH remains constant for ALL data being retrieved it makes sense to calculate once in one (hidden) cell and refer to that cell thereafter instead of repeatedly recalculating the same value... eg

IU5: =MATCH($C$11,'ISOC-NK'!$O:$O,0)

Then the earlier formula becomes

=INDEX('ISOC-NK'!$A:$A,$IU$5)

the reason it worked for some records is more by luck than judgement I'm afraid (down to Binary Search Algorithm in the VLOOKUP with TRUE range_lookup type)

[ajocius]

Thank you so much for the quick reply. I'm sitting in Kirkuk, Iraq trying to show a large spreadsheet of useful data into a single page. I'll be working your suggestions the remainder of this evening and tomorrow. I'll probably have another question or two before it done. Thanks again for your assitance.

[ajocius]

DonkeyOte, your suggestions worked like a champ. You've propelled me from having a hard to read spreadsheet into a useful tool. Thanks so much.