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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VlOOKUP/MATCH/INDEX 2
vlookup or index/match formula?? 2
Match Index Choose 1
vlookup error 2
vlookup and match first value greater than 4
Index Match question. 6
Match then Vlookup 2
Vlookup, Index & Match 1

Back
Top