VLOOKUP higher value

[twiggywales]

Hi,

probably a noobie question but when I'm doing a vlookup with true as the range lookup is there a way to make it pick the value above it not the value below.


example

vlookup(500,A2:A6,1,TRUE) = 450

A
300
325
450
525
600

I want it to go up to 525

[NBVC]

You could use:

=INDEX(A2:A6,MATCH(450,A2:A6,1)+1)

but if there is an exact match, it will go to the next,.... if you don't want that, then...

=INDEX(A2:A6,MATCH(TRUE,INDEX(A2:A6>=450,0),0))

[twiggywales]

ok its a tiny bit more complex

let me post up a book to show you

I need the cell length (column L) to return an exact match or the next value up from the table on sheet 3 depending on the embedment


so length and embedment ( if E6+100 = any of the numbers on the first column of table then show that if not then show the next size up)


my formula isn't nice so please help

HDBOLTS.xlsx

[NBVC]

Try

=INDEX(Sheet3!$A$3:$A$10,MATCH(TRUE,INDEX(Sheet3!$A$3:$A$10>=E6,0),0))

or if you need to add 100 to E6 first...

=INDEX(Sheet3!$A$3:$A$10,MATCH(TRUE,INDEX(Sheet3!$A$3:$A$10>=E6+100,0),0))

[twiggywales]

that works a charm. i dont suppose you could explain a little how this works, i dont really understand how to make it work and id like to so that in the future i can do this myself.

[NBVC]

This part:

INDEX(Sheet3!$A$3:$A$10>=E6,0)

creates a range of TRUE/FALSE results based on the condition of checking each of Sheet3!$A$3:$A$10 for >=E6.

The MATCH in MATCH(TRUE,INDEX(Sheet3!$A$3:$A$10>=E6+100,0),0) then checks for the first TRUE match in that range, which would coincide with the first time one of Sheet3!$A$3:$A$10 is greater than or equal to E6 value. The result of the MATCH() would represent the position number in that range the match is found.

Then the final INDEX(Sheet3!$A$3:$A$10 part will index that postion number to the range of values to get the right one.

[twiggywales]

im not 100% sure how that works still, but i think i understand a little more.

thanks for the help. if you have any further information on how to use the match and index functions id love to have a read over it.

thanks

[NBVC]

Here is a good article on the functions used separately and combined...

http://www.contextures.com/xlfunctions03.html

[twiggywales]

i would like to thank you very much for this info. the site helped me to actully understand what im doing with match and index.


really thank you so much