Lookup with comparing values ?

[kostas]

I have a table with two columns. The second column has numbers.

I am trying to do a lookup that will check to see which number after my lookup value is +3 bigger.

example

a 11
b 5
c 5
d 6
e 10
f 9

I am looking up "b". I want to return "e" because (a) it is under b in the sequence and (b) 10-5>=3. Is there a way to do this with lookup? I checked the forum but couldn't find anything about lookup and difference in values.

Thank you in advance.

Kostas

[NBVC]

if your table is in A1:B6 then try:

Please Login or Register  to view this content.

adjust ranges to suit and change "b" to cell reference housing the lookup character.

Formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER...you'll see { } brackets appear around the
formula.

Note: what happens when there is no "bigger than 3" value?

[kostas]

NBVC,

Thank you very much for your reply.

A question though. At the formula you are giving, in the latest MATCH you have E1, what is that supposed to be? I suppose it's my "b" ? I changed it accordingly but if there is any higher value on the table than the last record's it will give me an error.

In case there is no bigger than three value it should return something like "NOTHING" or "NO VALUE" or whatever I assign it to.

Please have a look at the attached spreadsheet to get a better idea although I believe you have understood what I want to do exactly.


Regards,

K

[NBVC]

Yes, you are right, it should've been "b" instead of E1...I was using that cell ref for my testing....

I don't have access to Excel on the computer I am on, but I think this should work to return "No Value" if an error is returned.

Please Login or Register  to view this content.

[kostas]

EDIT: Looks like I have figured it out.

Based on your formula I just changed (added) the FALSE switch on the vlookups and it seems to work. Otherwise it was taking the lookups as TRUE and thus searching for the equal or greatest lower value. With the FALSE it looks for the exact one (plus higher one which is what we wanted).

It seems to be working now. Can you please confirm if based on your formula-concept is the required fix?


New formula:

Please Login or Register  to view this content.

---------------------------------------
previous reply:



Something seems not to be working correct with the formula.

If the last record on the table is +3 or larger than the "b" value it automatically jumps to the last record on the table (i.e a6) and returns the value at b6. If it is not I get a "no value" return, regardless of whether there are other records that do satisfy the +3 criterion in between.

To visualise it imagine the following table (since you don't have office at the moment)


a 27
b 12
c 12
d 12
e 17
f 20
g 12
h 29

Since I am checking my table starting from the record "b" with value 12 and my criteria is to seek the immediately next record that has a value of +3 or more to "b", I would expect to get "e" as a result.

The formula you provided (and for which I thank you very much) jumps to "h" as long as h>=b+3. If h is under 15 I get a "no value".

I hope it makes sense.

Thank you and any further ideas appreciated.

K

[NBVC]

Depending on your "real data" you may be correct...

Your sample data has the first column in ascending order and so in that case you wouldn't need the "False" qualifier according to how Vlookup works. But if your first column is not in ascending order then you would need the "False".

I tested your last set of sample data with my previous formula and after adjusting the ranges to suit the expanded dataset, I got "e" result as expected...it is safer, probably to just add the False to ensure that exact matches are found.