EXCEL: return 1 value from table based on 2 criteria being met

Question

I have a table of shipping prices per number of units and by country. I'd like to have the total shipping cost appear on my order form once the number of bottles and country have been filled in elsewhere on the order form. Any ideas for what formula would do this?

Here's a small part of my table:
           1           2           3          4             5         6
France   €12.60      €14.40      €17.40      €19.44      €21.36      €23.40 
Belgium  €20.40      €26.40      €31.20      €36.60      €42.00      €46.80 
Germany  €20.40      €26.40      €31.20      €36.60      €42.00      €46.80 

So for the client shipping to Germany that selects 5 units, how do I get €42.00 to appear in the shipping cost cell?

Thank you

BigBen · Accepted Answer · 2019-10-18 18:22:48Z

1

INDEX with two MATCH's.

=INDEX(A1:G4,MATCH("Germany",$A$1:$A$4,0),MATCH(5,$A$1:$G$1,0))

answered Oct 18, 2019 at 18:22

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Nevins Over a year ago

Substitute the text "Germany" and "5" for references to the cells where these values are entered in your order form and put the formula where you want your shipping value show up.

Terry W · Accepted Answer · 2019-10-21 11:25:36Z

0

Here is another approach...

Suppose your data is in range A1:G4 including headers,

Put your look up criteria in range A6:B7, and use the following formula to return the desired cost:

=DGET(A1:G4,""&B7&"",A6:A7)

answered Oct 21, 2019 at 11:25

Terry W

3,2872 gold badges10 silver badges24 bronze badges

Collectives™ on Stack Overflow

EXCEL: return 1 value from table based on 2 criteria being met

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related