CODEWITHSUNDEEP
excel
detroitwilly
detroitwilly

Reputation: 821

2-Way Table Look Up in Excel Using VLOOKUP and MATCH Returning Error

I'm working an a cell formula that will perform a 2 dimensional look-up on table.

My the formula is as follows:

=VLOOKUP(A97,A4:L10,MATCH(B96,A4:L4,0),FALSE)

However, it is returning a #N/A error. I'm wondering if it has something to do with formatting, but I can't say for sure. In any case, the column headers of my table array are formatted as numbers (years), the row labels are formatted as text, and the actual data in the array is a custom format with the data pulled from other sheets in the workbook.

Is it something with formatting, or is that not a factor? If not, is there something wrong with the formula itself?

Thanks.

-Sean

Upvotes: 0

Views: 2927

Answers (1)

detroitwilly
detroitwilly

Reputation: 821

Credit to @barryhoudini and @DaveSexton for the solution... I'm just documenting it officially.

First off, the error I was receiving was due to the formatting of the source cell. So, per Barry's suggestion, I concatenated a "" to the end of my cell reference, which formatted the source cell as text, allowing the function to work. New code:

=VLOOKUP(A97,A4:L10,MATCH(B96&"",A4:L4,0),FALSE)

Second, per Dave's suggestion, I abandoned the VLOOKUP/MATCH approach in favor of the INDEX/MATCH/MATCH approach. E.G:

=INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0))

Thanks both for your help.

Upvotes: 1

Related Questions