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:

		Item			Score
1	Marine	Item 1	Page ref		20
2	Marine	Item 2	Page ref		100
3	Marine	Item 3	Page ref		40

<tbody>
</tbody>

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.