Software

Ask something
Discussion Locked

Excel 2000: VLookup Command

0 helpful
3 replies

by jbartlett

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
When using the VLookup Command to return a value based on a column of data, the default answer (when an exact answer is not available) will be the answer with the closest yet smaller value based on the data given. I want to run the VLookup command and have the value returned be the closest yet greater value. I'm lost on this one.

3 replies

by stillatit

You currently have a key column and one or more data columns. Create a second key column, with values copied from the current key column, but offset by one row. If you do not get an exact key match, search using the second key column.

I am using dashes rather than spaces below, so the editor does not squeeze them out.

Key1 Key2 Data....

1-----0-----data1
5-----1-----data2
10----5-----data3

Your search is done with an iif statement, which has the syntax
iif(condition, value if true, value if false).

iif(vlookup my value on key1 is equal to the key1 value I find, vlookup on key1 and return the data field, vlookup on key2 and return the data field)

Good luck.

This discussion is closed to new replies.