Vlookup issue

[Helix242]

Hi all,

I hope you can help as I appear to be missing something.

I have a value in CALC!C8 which I am trying to look up. =VLOOKUP(CALC!C8,VALIDATION!A6:C10,3,0)

So in the VALIDATION SHEET

A6 =0% B6 =50% C6 =1
A7 =50.01% B7 =60% C7 =2
A8 =60.01% B8 =65% C8 =3
A9 =65.01% B9 =70% C9 =4
A10 =70.01% B10 =75% C10 =5

Using the above formula returns a #N/A error

Using =VLOOKUP(C8,VALIDATION!A6:C10,3) returns the correct error, but when CALC!C8 is greater than 75% it still returns 5.

Can anyone help? I'm sure this is really simple, but I am struggling with this.

Thanks in Advance

 

[Michael M]

If you are looking up col 3 in your VALIDATION table, anything above 70% IN C8 will ALWAYS return 5 !
What did you expect the answer to be, if C8 was greater than 75% ??

 

[DanteAmor]

The last parameter of the formula can be 0 or 1.

=VLOOKUP(CALC!C8,VALIDATION!A6:C10,3,0)


If you indicate 0, then the match is exact.



If you indicate 1 or omit the value, the search is approximate.
In this case, any value greater than 70.01% in the approximate search will always be 70.01, which is why it returns you 5.


If you want to search between the ranges, you can use any of these formulas:

=IF(CALC!C8>VALIDATION!B10,"Out of range",SUMPRODUCT((VALIDATION!A6:A10<=CALC!C8)*(VALIDATION!B6:B10>=CALC!C8)*(VALIDATION!C6:C10)))

Or

=IF(CALC!C8>VALIDATION!B10,"Out of range",SUMIFS(VALIDATION!C6:C10,VALIDATION!A6:A10,"<="&CALC!C8,VALIDATION!B6:B10,">="&CALC!C8))

 

[Helix242]

Thank you for this, however if I do use =VLOOKUP(CALC!C8,VALIDATION!A6:C10,3,0) this returns #N/A no matter what value is in CALC!C8.

The last parameter of the formula can be 0 or 1.

=VLOOKUP(CALC!C8,VALIDATION!A6:C10,3,0)


If you indicate 0, then the match is exact.



If you indicate 1 or omit the value, the search is approximate.
In this case, any value greater than 70.01% in the approximate search will always be 70.01, which is why it returns you 5.


If you want to search between the ranges, you can use any of these formulas:

=IF(CALC!C8>VALIDATION!B10,"Out of range",SUMPRODUCT((VALIDATION!A6:A10<=CALC!C8)*(VALIDATION!B6:B10>=CALC!C8)*(VALIDATION!C6:C10)))

Or

=IF(CALC!C8>VALIDATION!B10,"Out of range",SUMIFS(VALIDATION!C6:C10,VALIDATION!A6:A10,"<="&CALC!C8,VALIDATION!B6:B10,">="&CALC!C8))

 

[Michael M]

As Dante has mentioned, by using 0 in the formula, the result MUST be an EXACT match, or you will get the #N/A error.

 

[ClaireS]

You don't really need the figures in column A at all if you are doing an INEXACT match (without optional 4th argument), so I would delete those and move the cells in B:C left so that they are in A:B. You want the values 50,60,65,70,75 in column A and 1,2,3,4,5 in B, then return the second column. The Inexact match will take the lookup value from CALC!C8 and try to match it in column A. If it finds the value exactly (say 65) it will return the match (3). If it doesn't find the match, it will return the value equivalent to the next lower entry (69.99 would still return 3). In your example, the highest lookup figure is 75, so any value above that must return 5. Just add more entries if you want more results.

For an Inexact match lookup to work, the values in the lookup column must be sorted into ascending order.

 

[Helix242]

Thank you all for your prompt response, guidance and helpful comments. It’s much appreciated.

 

[Helix242]

Thank you Claire. ?

 

[DanteAmor]

Thank you all for your prompt response, guidance and helpful comments. It’s much appreciated.

Your welcome & thanks for the feedback.