Vlookup for range

**hermithead** · 03-10-2010, 08:44 AM

Im looking to expand this vlookup so if it doesnt find the value associated with the specific order category that I want returned it will instead retrun an order category according to a defined range say values between 200000-299999.

=VLOOKUP(A2,D:E,2,FALSE)

Probably best to view the attached sheet.

**NBVC** · 03-10-2010, 08:51 AM

It is recommended to only use your lower bounds in one column and to list them in ascending order... so the 200000 would be the top followed by 300000 next and so on, then you can use

=Vlookup(A2,D:E,TRUE)

If you have to show it as a range 200000-299999, then you still need to place in ascending order and then use something like:

=INDEX($E$2:$E$44,MATCH(A2,LEFT($D$2:$D$43,6)+0))

confirmed with CTRL+SHIFT+ENTER not just ENTER...

notice, that you cannot use whole column references with this type of formula (in XL2003 or earlier)

**hermithead** · 03-10-2010, 09:04 AM

Originally Posted by NBVC

It is recommended to only use your lower bounds in one column and to list them in ascending order... so the 200000 would be the top followed by 300000 next and so on, then you can use

=Vlookup(A2,D:E,TRUE)

Am I missing something or does this only return the value ie 200000 and NOT the number category its supposed to be looking up and returning which should be '0'?

**NBVC** · 03-10-2010, 09:24 AM

See attached for what I mean...

**hermithead** · 03-10-2010, 09:47 AM

Brilliant! Thanks NBVC I've been looking for this formula for a looooooooooooong time. If you have the time can you explain to me how by using TRUE it returns everything from 200000-299999?

**NBVC** · 03-10-2010, 09:59 AM

Per Excel Help on the Function...

That 4th argument defines what kind of a lookup it will be.. an exact match or an approximate match... FALSE would indicate an EXACT match and the list can be arranged in any order.. only an exact match is searched for... else you will get #N/A error.

The TRUE indicates to find an approximate match... and it will look for the last value that is smaller than or equal to the search value.. when the search list is in ascending order.

So anything higher than 200000 but lower than 300000 (say 250000) will default to the 200000 value since it is the last number that is lower than 250000 and not greater.