Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup or Index Match ? | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Vlookup, index, match? | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |