VLOOKUP and MATCH returning incorrect values

**-Spax-** · 10-10-2011, 05:54 AM

Hello all, I've searched the forums before to solve my problems but this time I need to post.
I'm just getting to grips with Excel having used it for fairly simple calculations in the past but now, in a a new job, I find myself getting more in-depth.

I'm using Excel 2010 and having difficulty with vlookup and match.

Data in a table is referenced by two values in two columns, the coordinates of which should provide the answer.
But.
The values returned are offset by one, returning a different value.

I've searched the forums and found many similar problems but have been unable to relate them to my own issue (At this point I think I'm suffering spreadsheet exhaustion!).

Attached should be an image representing the issue and a copy of the workbook in xls (97-2003) and xlsx (2010) formats.

Thanks in advance.

**quekbc** · 10-10-2011, 06:10 AM

Hi Spax,

The formulas are all correct, however the array refenrence in the MATCH function is of a different height to that of the HLOOKUP function. Because the HLOOKUP function includes the first row as its 3rd parameter, you should either +1 to the MATCH function or change the reference in C5:C20 from $F$6:$F$19 to $F$5:$F$19

**tom1977** · 10-10-2011, 06:12 AM

look at atachment (1 variant)

regards

**quekbc** · 10-10-2011, 06:41 AM

-- deleted --

**-Spax-** · 10-10-2011, 07:03 AM

Gentlemen, thank you both!

I arrived with one problem and get two solutions!

quekbc; It hadn't occurred to me that the function would want to see the other heading.
By increasing the reference area by one to include the row or column containing the headings (depending on the V or H lookup) it returns the correct value.
This fixes the incorrect value result I was receiving with both V and H lookup.

tom1977: Being unfamiliar with the INDEX function this has opened up my options. INDEX could be a more elegant solution as I think it could provide a more encompassing solution to future data tables.

Thank you both again for your help