VLOOKUP problem?

[Francis Hookham]

VLOOKUP problem



Given Column I (C7) has a range of numbers, typically from 47 to 471

(The width of items to be packed in a box).



Given a number of box sizes in a Range named Bwsizes, typically:

1 140

2 165

3 190

.. .

.. .

11 390

12 415

13 440

14 465

15 490



Required to enter into Column H (C8) the smallest box into which a item will
fit.



I thought I should be able to do this with VLOOKUP but I've drawn a blank so
far.



Please help - there are 2,000 of them and there's the depth and height in
other columns to sort from Ranges BWsizes and BHsizes! The size Ranges are
on another sheet.



Or is VLOOKUP not the right function for this?



Francis

 

[Francis Hookham]

Hold on - I think I might have cracked it:

=VLOOKUP((MATCH(B3-1,MatchRange,1)+1),NewRange,2)

(there is a reason for the -1 in B3-1)

OK - references are not the same but this is in a trial sheet - I'll come
back if it does not work.

Of course if you have any better suggetestion please tel me.

Francis

 

[Stopher]

Index may also be a better function for this.

 

[Francis Hookham]

Should I leave well alone and get on or ask you to explain how?

There's an other question just posted about copying a range elsewhere which
is more important just now - but later it would be good to improve on what I
have.

Thanks

Francis Hookham