I am trying to populate a field based on matching 2 different lists. I want
to populate a cell with the cost per mile based on a drop down box for a
carrier name then a drop down box for destination. For example, if I select
carrier 'ABC' in my drop down box in cell a2, then select my destination
'Atlanta' from drop down box in cell a5, I want the rate per mile in cell a7
to populate from a seperate rate sheet in the workbook. I can't figure out
how to write a formula in cell a7 to look at 2 different lists. The 'rate
sheet' list has multiple carriers with multiple destination. Is this possible
and can someone help?

It's pretty easy to use IF with VLOOKUP. I don't understand the problem.
What are the specific criteria you wan to use to choose the correct list? If
it's Atlanta and carrier ABC then use List A, and for everything else use
List B?

Also, what do the lists look like? Are they identical in terms of the
column structure? Can you provide a rough sample of the data in the lists?

"cra88" wrote:

I am trying to populate a field based on matching 2 different lists. I want
to populate a cell with the cost per mile based on a drop down box for a
carrier name then a drop down box for destination. For example, if I select
carrier 'ABC' in my drop down box in cell a2, then select my destination
'Atlanta' from drop down box in cell a5, I want the rate per mile in cell a7
to populate from a seperate rate sheet in the workbook. I can't figure out
how to write a formula in cell a7 to look at 2 different lists. The 'rate
sheet' list has multiple carriers with multiple destination. Is this possible
and can someone help?

Hi,

You really haven't given us enough info, how is the data stored on the rate
sheet? I am going to assume a vertical layout with column A containing the
Carrier, column B containing the Destination, and column C containing the
rate per mile.

In A7 enter the following formula

=SUMPRODUCT(--(A2=Sheet2!A1:A14),--(A5=Sheet2!B1:B14),Sheet2!C1:C14)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"cra88" wrote:

I am trying to populate a field based on matching 2 different lists. I want
to populate a cell with the cost per mile based on a drop down box for a
carrier name then a drop down box for destination. For example, if I select
carrier 'ABC' in my drop down box in cell a2, then select my destination
'Atlanta' from drop down box in cell a5, I want the rate per mile in cell a7
to populate from a seperate rate sheet in the workbook. I can't figure out
how to write a formula in cell a7 to look at 2 different lists. The 'rate
sheet' list has multiple carriers with multiple destination. Is this possible
and can someone help?