VLOOKUP, INDEX, MATCH... What to choose
 

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)


"Piloulondon" wrote in message
...
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.

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" wrote:

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)


"Piloulondon" wrote in message
...
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.

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(Sh eet2!$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)


"Piloulondon" wrote in message
...
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" wrote:

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)


"Piloulondon" wrote in message
...
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.