VLOOKUP, INDEX, MATCH... What to choose

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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
 
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)
 
Back
Top