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

#### Niltiac88

##### New Member
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)

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Formula works for me... not sure what the problem is... sorry.

Last edited:

Try

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

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.

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.

Replies
8
Views
703
Replies
4
Views
309
Replies
3
Views
435
Replies
3
Views
164
Replies
10
Views
702

1,196,325
Messages
6,014,650
Members
441,834
Latest member
GHOSTOF309

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back