Help with multiple values for a lookup

[Guest]

Hi there,

Apologies if this is a repeat but I cant find my first entry on the
discussion site.

Problem:
I have 2 workbooks. The first contains daily sales figures and I need to
look up freight charges stored on workbook 2 based on 1. Location and 2.
Product type.

Workbook One looks somthing like
Location Prod 1 Prod 2 Prod 3 Prod 4 etc Freight
Auckland 10 Value
determined by Loc & Prod
ChCh 60
Etc

Locations held in column D and products in E:N

I have unsuccesfully tried Index and Match as not really sure what I am
doing. Can anyone please help?

Can email examples if required.

Many thanks for your time.

 

[Bob Phillips]

=INDEX([VBAX.xls]Sheet1!$A$2:$A$20,MATCH(1,(D1=[VBAX.xls]Sheet1!$C$2:$C$20)*(E1=[VBAX.xls]Sheet1!$D$2:$D$20),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

Appreciate your answer but I am going to show my total ignorance now.....Can
you explain a wee bit about what the formula below is doing and which bits
relate to the 2nd workbook of mine where the freight values are stored.

The 2nd workbook is laid out eg
Location Prod 1 Prod 2 Prod 3 Prod 4 etc
Chch $1.0 $1.50 $3.00 $2.00
etc

I am a beginner at this so please excuse all the questions!

Thanks

=INDEX([VBAX.xls]Sheet1!$A$2:$A$20,MATCH(1,(D1=[VBAX.xls]Sheet1!$C$2:$C$20)*(E1=[VBAX.xls]Sheet1!$D$2:$D$20),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi there,

Apologies if this is a repeat but I cant find my first entry on the
discussion site.

Problem:
I have 2 workbooks. The first contains daily sales figures and I need to
look up freight charges stored on workbook 2 based on 1. Location and 2.
Product type.

Workbook One looks somthing like
Location Prod 1 Prod 2 Prod 3 Prod 4 etc Freight
Auckland 10 Value
determined by Loc & Prod
ChCh 60
Etc

Locations held in column D and products in E:N

I have unsuccesfully tried Index and Match as not really sure what I am
doing. Can anyone please help?

Can email examples if required.

Many thanks for your time.

 

[Bob Phillips]

[VBAX.xls] is the workbook, so replace that with yours

Sheet1 is the sheet within that workbook, so replace that

Columns A, C and D are columns within that sheet with the freight charges,
locations and product lists so replace them

Then all it does is compared the target location against the locations list,
the target product against the products list, and where both match you get a
row number which is used to index into the freight charges.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob,

Appreciate your answer but I am going to show my total ignorance
now.....Can
you explain a wee bit about what the formula below is doing and which bits
relate to the 2nd workbook of mine where the freight values are stored.

The 2nd workbook is laid out eg
Location Prod 1 Prod 2 Prod 3 Prod 4 etc
Chch $1.0 $1.50 $3.00 $2.00
etc

I am a beginner at this so please excuse all the questions!

Thanks

=INDEX([VBAX.xls]Sheet1!$A$2:$A$20,MATCH(1,(D1=[VBAX.xls]Sheet1!$C$2:$C$20)*(E1=[VBAX.xls]Sheet1!$D$2:$D$20),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi there,

Apologies if this is a repeat but I cant find my first entry on the
discussion site.

Problem:
I have 2 workbooks. The first contains daily sales figures and I need
to
look up freight charges stored on workbook 2 based on 1. Location and
2.
Product type.

Workbook One looks somthing like
Location Prod 1 Prod 2 Prod 3 Prod 4 etc Freight
Auckland 10 Value
determined by Loc & Prod
ChCh 60
Etc

Locations held in column D and products in E:N

I have unsuccesfully tried Index and Match as not really sure what I am
doing. Can anyone please help?

Can email examples if required.

Many thanks for your time.

 

[Guest]

Bob,

Thank you so much.

[VBAX.xls] is the workbook, so replace that with yours

Sheet1 is the sheet within that workbook, so replace that

Columns A, C and D are columns within that sheet with the freight charges,
locations and product lists so replace them

Then all it does is compared the target location against the locations list,
the target product against the products list, and where both match you get a
row number which is used to index into the freight charges.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob,

Appreciate your answer but I am going to show my total ignorance
now.....Can
you explain a wee bit about what the formula below is doing and which bits
relate to the 2nd workbook of mine where the freight values are stored.

The 2nd workbook is laid out eg
Location Prod 1 Prod 2 Prod 3 Prod 4 etc
Chch $1.0 $1.50 $3.00 $2.00
etc

I am a beginner at this so please excuse all the questions!

Thanks

=INDEX([VBAX.xls]Sheet1!$A$2:$A$20,MATCH(1,(D1=[VBAX.xls]Sheet1!$C$2:$C$20)*(E1=[VBAX.xls]Sheet1!$D$2:$D$20),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi there,

Apologies if this is a repeat but I cant find my first entry on the
discussion site.

Problem:
I have 2 workbooks. The first contains daily sales figures and I need
to
look up freight charges stored on workbook 2 based on 1. Location and
2.
Product type.

Workbook One looks somthing like
Location Prod 1 Prod 2 Prod 3 Prod 4 etc Freight
Auckland 10 Value
determined by Loc & Prod
ChCh 60
Etc

Locations held in column D and products in E:N

I have unsuccesfully tried Index and Match as not really sure what I am
doing. Can anyone please help?

Can email examples if required.

Many thanks for your time.