Looking up a value based on several index columns?

[Kimberlily307]

Is there some kind of vlooup/match/index function that allows you to lookup values using two columns of data?

For example, I want to use both AK and 4 to get the value 1200.

AK 1 100
AK 2 150
AK 3 800
AK 4 1200
CA 1 50
CA 2 300
CA 3 750
CA 4 900

I know I could do this by adding another column with something like column1.column2 as an index, but I am looking for something cleaner since it's going in a Macro.

Maybe I can find all the AK's, select that portion of the table, move my selection over, then vlookup the 4 just in that portion of my table? Seems so complicated...

[Pete_UK]

If you can guarantee that the combination only occurs once, then you can use this:

Please Login or Register  to view this content.

You could put the AK and the 4 into two cells, and use the cell references within the formula.

Obviously, you should change the range references in the formula to suit your data.

Hope this helps.

Pete

[FDibbins]

maybe the simplest way would be to add a helper column...=A1&B1, copied down, and then base a regular vlookup on that