VLOOKUP sorta works, but result is actually one cell up/or down.

[mmcclusk2]

I am trying to use a VLOOKUP on a table of manufacturing die cuts of certain sizes.

A formula tells me a number close to the die that is available. The rule is to use the next larger size die cut for the job.

An example of die sizes are:

Die Sizes
108.0
112.7
114.3
117.5

My formula gives me 114.22, therefore, I should use the 114.3 die.

However, the VLOOKUP says 112.7, not the answer I want.

How can I adjust this VLOOKUP to give me the next size down? I am guessing a IF, but I am not the best at writing IF statements.

Thanks for any help on this.

 

[Jonmo1]

Can you sort the Die Sizes in DESCENDING order (largest on top) ?

If so, use

=INDEX(A2:A10,MATCH(114.22,A2:A10,-1))

 

[Aladin Akyurek]

What is the formula you have tried?

 

[mmcclusk2]

Can you sort the Die Sizes in DESCENDING order (largest on top) ?

If so, use

=INDEX(A2:A10,MATCH(114.22,A2:A10,-1))

Yep, resorted list big to small.

WOW! That worked so much better!

THANKS! Never used MATCH or INDEX before.

ray:

Thanks!!!!

 

[Jonmo1]

Glad to help, thanks for the feedback.