Using choose and vlookup to find a value

[GIS2013]

Hi,
In the sample file my question is explained.
Basically I am trying to use vlookup-choose-array to look up two columns.
Any help will help.

[DBY]

Hello
Maybe using SUMPRODUCT to find the column number, as follows:

Please Login or Register  to view this content.

*Note the two commas (red) as there is no range for 2.

Hope this helps.
DBY

[GIS2013]

@DBY - thanks a lot. awesome.

More generally - DBY's solution tansforms - virtually speaking, using choose - a matrix (2*2) into a 2-column range, something vlookup can handle easily.

[GIS2013]

@DBY - it's a bit complex, please explain me the parameters of the choose function in the context of your formula of course: index and values. Why hard coding the headers and why value2 is missing?

[GIS2013]

Understood.

[DBY]

The CHOOSE function accepts numbers between 1 and 254 as an argument. The Values that you define, in this case cell ranges, are then chosen depending on the number 1 - 254. As the second VLOOKUP range was in column 3, the letters C and D the SUMPRODUCT function returns 3, so we want the 3rd. Value not the second which has been left blank.

The Column headers just return the number of the column matching the letters in cells A5:A8. I made the reference absolute out of habit but here it doesn't really matter as long as the numbers correspond with the CHOOSE Index number. You could just put {1,2,3,4}, rather than use the COLUMN function.

Not sure if my explanation is clear but I hope it helps.

DBY

[GIS2013]

Yes it's very clear. The nested COLUMN confused me a little bit but it's really ok now.