Vlookup not selecting the correct cell

[nathandavies9]

Hi All,

I have been doing some testing on a worksheet and have noticed my VLOOKUP is not selecting the correct cell as it should, it should be selecting the next cell down because the value is higher than the data match.

I have attached my workbook for your assistance with highlighted cells.

Cell I6 = 190A so it should select Cell I15 but at the minute it is selecting I14 which is not the correct cell because Cell I6 is larger than cell I14.

Any help with this would be greatly appreciated.

[Jonmo1]

Which cell contains your formula?

[nathandavies9]

Its AC6 on sheet FORM1-TEMP

[JohnTopley]

I can't find your VLOOKUP but I (suspect) it is working correctly as it will return the value that is LESS THAN the search value if there is not an exact match.

EDIT: INDEX/MATCH not VLOOKUP but above applies.

[Jonmo1]

The MATCH function using the 'closest match' lookup type, finds the largest value that is LESS than the lookup value.
So it is returning the correct value

If you want it to return the next highest value, you have a few choices.
1) Add 1 to the result of the match
=IF(I6*1.25>DATA!I60,"DISTBLOCK",INDEX(DATA!I5:I20,MATCH('FORM-1 TEMP'!I6,DATA!J5:J20)+1))

2) Offset the index range by 1 row
=IF(I6*1.25>DATA!I60,"DISTBLOCK",INDEX(DATA!I6:I21,MATCH('FORM-1 TEMP'!I6,DATA!J5:J20)))

3) Sort the lookup table (I5:J20 in Descending order by column J), and use -1 as the match type
=IF(I6*1.25>DATA!I60,"DISTBLOCK",INDEX(DATA!I5:I20,MATCH('FORM-1 TEMP'!I6,DATA!J5:J20,-1)))

[nathandavies9]

All I have got this wrong! it is not a VLOOKUP at all, it is INDEX MATCH!!

Sorry for the confusion!!

[nathandavies9]

Jonmo1, I have gone with the first option as this worked first time!

Thank you for your help!!

[Jonmo1]

You're welcome.

Keep in mind you will have #N/A errors if:
A) The lookup value is Greater than the last value in the column, J20
B) The lookup value is Less than the first value in the column, J5

[nathandavies9]

I have changed my range so if it is bigger or smaller it will return 0 or COPPER.

Thanks again.

[nathandavies9]

I think i may have clicked solve a bit too quick, i'm not sure how I would change the formula so if it was less that J5 to show the botttom value cell.

so if Cell I6<= CEll J6 it should select Cell I5

=IF(I6>DATA!J57,"DISTBLOCK",INDEX(DATA!I5:I20,MATCH('FORM-1 TEMP'!I6,DATA!J4:J20)+1))