VLOOKUP, INDEX, MATCH... What to choose

[Guest]

Hi everybody.
Here is table on sheet1
A B C D E
F G
1Order Nr Cust PO Cust Name Order Date Post Code Volume Price/Liter
2 SY 12000 ?
3 DE 6000 ?
4 WW 40000 ?
5 FM 20000 ?
6 M 35000 ?

Here is table on sheet 2:
A B C D E
1 Postcode 0-1000 1001-2000 2001-3000 3001-4000
2 SY 1 2 3 4
3 DE 8 9 10 11
4 WW 15 16 17 18

What formula should I use to have sheet1:G populated automatically.
The choice is made depending on the Pose Code and the Volume.

Many thanks.

 

[Bob Phillips]

Try this

=INDEX(Sheet2!B2:H8,MATCH(A2,Sheet2!A2:A8,0),INT((B2-1)/1000)+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob, sent you an email with the XL file attached to it. Hope you will have
the chance to have a look at it.
Many thanks.
Philippe

 

[Bob Phillips]

Hi Philippe,

My divisor was a factor of 10 too small.

I have also added some error handling to the formula. This is what it looks
like now

=IF(ISNA(MATCH(E2,Sheet2!$A$2:$A$8,0)),"",INDEX(Sheet2!$B$2:$H$8,MATCH(E2,Sh
eet2!$A$2:$A$8,0),INT((F2-1)/10000)+1))

--

HTH

RP
(remove nothere from the email address if mailing direct)