Find more than specific integer in vlookup

[jaryszek]

Hi,

i have worksheet like here:

Screenshot_18.png

user is choosing number in yellow fiekd and cost is calculated in column J (where N/A in the screen is). In column J there is a vlookup formula.
The issue is when i have more than 128, like for example 256 choosen, i am getting "N/A" because there is no matchin row in lookup table.

In source table there is no 256 in key, there is only 128 with "+" like here:
Screenshot_19.png

Vlookup or different formula should know that when we have + next to number it can be 128 and more as match.

Example workbook in attachment like always.

Please help,
Jacek

[MrShorty]

I notice that you are using an exact match lookup (4th argument of VLOOKUP() is FALSE/0). What you are describing could be accomplished using an approximate match lookup (4th argument of VLOOKUP() is TRUE/1/omitted). The challenge will be thinking through your "text-number" type lookup values and understanding exactly how to build those text strings so that they can be reliably sorted and searched. For example, with the 3 example records, you could maybe do

Please Login or Register  to view this content.

Then edit your existing VLOOKUP() formula to something like =VLOOKUP("Windows-"&TEXT(C3,"000"),Table1,3,TRUE). 3 digit numbers (leading 0s as needed) are used to insure that the search works correctly for numbers up to 999 (Excel will see it as a pure text sort order and not really see the numbers).

For the oversimplified example you have provided, that should work. You will need to think carefully about exactly how to put together the text strings so they work correctly in your actual sheet, but the idea would still be the same.

[RodyaRaskol]

I adjusted your example to use a sumif function instead, source table is adjusted to include a product name " Windows" and a lower and upper bound for which the price applies

[jaryszek]

thank you very much!!

I prefer RodyaRaskol solution - is very clean. I would put as Upper bound thesame number like in LB , for example 5 and 5 and change formula to:

Please Login or Register  to view this content.

MrShorty your solution is nice also.

Best,
Jacek