VLOOKUP returning value from row above - have tried several solutions...

[Niltiac88]

Hi all, hope someone can help me - it's quite urgent and i'd really appreciate some help.

I am using a VLOOKUP and MATCH formula for a survey:
=VLOOKUP((MATCH(100,'2. Upwelling-downwelling'!H26:H38, 0)),'2. Upwelling-downwelling'!C26:K38, 3, FALSE)

It is designed to look in the worksheet (named 2. Upwelling-downwelling), find which item from a list has been given a score of 100, and copy the name of the item into a summary worksheet.
The table it is looking in in '2. Upwelling-downwelling' is laid out as below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Marine[/TD]
[TD]Item 1[/TD]
[TD]Page ref[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Marine[/TD]
[TD]Item 2[/TD]
[TD]Page ref[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Marine[/TD]
[TD]Item 3[/TD]
[TD]Page ref[/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

When respondents put 100 in the score column, the formula should return the name of the relevant item from the Item column in a separate Summary worksheet (i.e. in the above example 'Item 2' should be copied into the Summary worksheet).

However, at the moment, it is returning the value in the row above - Item 1.

This is only happening in one out of 22 worksheets which are all using the same formula. All the others are functioning perfectly as far as I can tell. An example of how the formula is written for a different (functioning) worksheet is:
=VLOOKUP((MATCH(100,'6. Biomass - production'!H26:H31, 0)),'6. Biomass - production'!C26:K31, 3, FALSE)

I can't see any difference...

Any suggestions much appreciated. I have already checked that the formula says 'false' or '0' and checked for unwanted spaces in the text, I have also tried changing the cell format to number. No luck.

I'd be very grateful if anyone can figure this out promptly - it will save much hassle if I can find a solution before all the survey respondents return the survey and I have to get them to do some of it again.

Many thanks, Caitlin (niltiac88)

 

[CodeNinja]

Formula works for me... not sure what the problem is... sorry.

 

[Jonmo1]

I think you're 'overthinking' your task..

Try

=INDEX('2. Upwelling-downwelling'!E26:E38,MATCH(100,'2. Upwelling-downwelling'!H26:H38,0))

 

[Niltiac88]

Formula works for me... not sure what the problem is... sorry.

Thanks anyway.

Given that it's only an issue in one worksheet, do you think it could be a formatting thing in that particular worksheet?

Excel's not my forte.

 

[Niltiac88]

That seems to work! Thanks very much. I'll test it out and post again if any issues, but seems to be a solution...
Very weird that it was only playing up for that one worksheet though.