Lookup Question

[missdeannamarie]

Hello!

I am really hoping someone can help me out with an excel question I am stumped on!

I will do my best to try and explain what I am trying to accomplish. I have a file with 3 different tabs. Tabs 2&3 are purely informational, which I want to pull data from. I need a formula that will take two selections from Tab 1, and reference those in Tabs 2&3 in order to pull the correct data. Each tab has column headers.

Here is what I am trying to do:

In Tab 1, when a customer is entered (Tab 1: Col B), I'd like a formula that will look up that customer in tab 2 (Tab 2: Col A), and find what freight rate they are assigned (Tab 2: Col B), then based on the type of freight entered in tab 1 (Tab 1: Col C), it would take the rate that is assigned to the customer and enter in the rate that corresponds to the freight type in tab 3 (Tab 3: Col B-F). I'd like that data returned in (Tab 1: Col J)

Basically, I need the rate in Tab 1: Col J to change based on the customer chosen.

I'd appreciate any help!!! Thanks!!!


TAB 1 - DATA
[TABLE="class: grid, width: 591"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I [/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE SHIPPED[/TD]
[TD] CUSTOMER [/TD]
[TD]TYPE OF FREIGHT[/TD]
[TD]ITEMS DELIVERED: COMMENTS/DETAILS[/TD]
[TD] QTY [/TD]
[TD]UNIT OF MEASURE[/TD]
[TD] LENGTH (FT) [/TD]
[TD] WIDTH (FT) [/TD]
[TD] SQ. FT. CALC. [/TD]
[TD] RATE [/TD]
[TD] COST [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/17/2017[/TD]
[TD]CUSTOMER A[/TD]
[TD]TYPE A[/TD]
[TD]ABC[/TD]
[TD]1.00[/TD]
[TD]ea[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/18/2017[/TD]
[TD]CUSTOMER B[/TD]
[TD]TYPE B[/TD]
[TD]DEF[/TD]
[TD]1.00[/TD]
[TD]ea[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/19/2017[/TD]
[TD]CUSTOMER C[/TD]
[TD]TYPE C[/TD]
[TD]GHI[/TD]
[TD]1.00[/TD]
[TD]ea[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/20/2017[/TD]
[TD]CUSTOMER D[/TD]
[TD]TYPE D[/TD]
[TD]JKL[/TD]
[TD]1.00[/TD]
[TD]ea[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/21/2017[/TD]
[TD]CUSTOMER E[/TD]
[TD]TYPE E[/TD]
[TD]MNO[/TD]
[TD]1.00[/TD]
[TD]Sq Ft[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/22/2017[/TD]
[TD]CUSTOMER F[/TD]
[TD]TYPE F[/TD]
[TD]PQR[/TD]
[TD]1.00[/TD]
[TD]Sq Ft[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1/23/2017[/TD]
[TD]CUSTOMER G[/TD]
[TD]TYPE G[/TD]
[TD]STU[/TD]
[TD]1.00[/TD]
[TD]Sq Ft[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

TAB 2 - CUSTOMERS
[TABLE="class: grid, width: 575"]
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]RATE TYPE[/TD]
[TD]Address[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[TD]Attachments[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CUSTOMER A[/TD]
[TD]RATE 1[/TD]
[TD]555, FAKE LANE, WA 55555[/TD]
[TD]555-555-5555[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CUSTOMER B[/TD]
[TD]RATE 2[/TD]
[TD]123, FAKE LANE, WA 55555[/TD]
[TD]555-555-5556[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CUSTOMER C[/TD]
[TD]RATE 3[/TD]
[TD]777, FAKE LANE, WA 55555[/TD]
[TD]555-555-5557[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CUSTOMER D[/TD]
[TD]RATE 4[/TD]
[TD]888, FAKE LANE, WA 55555[/TD]
[TD]555-555-5558[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]CUSTOMER E[/TD]
[TD]RATE 5[/TD]
[TD]999, FAKE LANE, WA 55555[/TD]
[TD]555-555-5559[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CUSTOMER F[/TD]
[TD]RATE 1[/TD]
[TD]111, FAKE LANE, WA 55555[/TD]
[TD]555-555-5510[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]CUSTOMER G[/TD]
[TD]RATE 1[/TD]
[TD]222, FAKE LANE, WA 55555[/TD]
[TD]555-555-5511[/TD]
[TD]ABC@ABC.COM[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


TAB 3 - RATES
[TABLE="class: grid, width: 321"]
<colgroup><col><col span="5"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Freight Type[/TD]
[TD]RATE 1[/TD]
[TD]RATE 2[/TD]
[TD]RATE 3 [/TD]
[TD]RATE 4 [/TD]
[TD]RATE 5 [/TD]
[TD]Unit of Measure[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TYPE A[/TD]
[TD]$15.00[/TD]
[TD]$20.00[/TD]
[TD]$20.00[/TD]
[TD]$25.00[/TD]
[TD]$40.00[/TD]
[TD]ea[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TYPE B[/TD]
[TD]$35.00[/TD]
[TD]$40.00[/TD]
[TD]$40.00[/TD]
[TD]$45.00[/TD]
[TD]$70.00[/TD]
[TD]ea[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TYPE C[/TD]
[TD]$35.00[/TD]
[TD]$40.00[/TD]
[TD]$40.00[/TD]
[TD]$45.00[/TD]
[TD]$70.00[/TD]
[TD]ea[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TYPE D[/TD]
[TD]$15.00[/TD]
[TD]$20.00[/TD]
[TD]$20.00[/TD]
[TD]$25.00[/TD]
[TD]$40.00[/TD]
[TD]ea[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]TYPE E[/TD]
[TD]$1.65[/TD]
[TD]$1.85[/TD]
[TD]$1.85[/TD]
[TD]$2.65[/TD]
[TD]$8.60[/TD]
[TD]Sq Ft[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]TYPE F[/TD]
[TD]$1.65[/TD]
[TD]$1.85[/TD]
[TD]$1.85[/TD]
[TD]$2.65[/TD]
[TD]$8.60[/TD]
[TD]Sq Ft[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]TYPE G[/TD]
[TD]$1.85[/TD]
[TD]$2.00[/TD]
[TD]$2.00[/TD]
[TD]$2.65[/TD]
[TD]$8.60[/TD]
[TD]Sq Ft[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

where sheet3 has your rates sheet2 has your customers and sheet1 data
in J2 and copy down

=INDEX(Sheet3!$B$2:$F$8,MATCH(Sheet1!C2,Sheet3!$A$2:$A$8,0),MATCH(VLOOKUP(B2,Sheet2!$A$2:$B$8,2,0),Sheet3!$B$1:$F$1,0))

 

[missdeannamarie]

where sheet3 has your rates sheet2 has your customers and sheet1 data
in J2 and copy down

=INDEX(Sheet3!$B$2:$F$8,MATCH(Sheet1!C2,Sheet3!$A$2:$A$8,0),MATCH(VLOOKUP(B2,Sheet2!$A$2:$B$8,2,0),Sheet3!$B$1:$F$1,0))

Thanks for the help but I am getting a #N/A return. Any ideas on why?

 

[Scott T]

Sounds like one of the match functions is not finding a match.

In edit mode highlight the red part and press F9 to evaluate it do the same with the blue part. DO NOT press enter use escape. When you evaluate it will return the results of the formula so you know what is causing the #NA error. Check the cell references to make sure they are correct for your data.

=INDEX(Sheet3!$B$2:$F$8,[COLOR=#ff0000]MATCH(Sheet1!C2,Sheet3!$A$2:$A$8,0)[/COLOR],[COLOR=#0000CD]MATCH(VLOOKUP(B2,Sheet2!$A$2:$B$8,2,0),Sheet3!$B$1:$F$1,0)[/COLOR])

 

[missdeannamarie]

Sounds like one of the match functions is not finding a match.

In edit mode highlight the red part and press F9 to evaluate it do the same with the blue part. DO NOT press enter use escape. When you evaluate it will return the results of the formula so you know what is causing the #NA error. Check the cell references to make sure they are correct for your data.

=INDEX(Sheet3!$B$2:$F$8,[COLOR=#ff0000]MATCH(Sheet1!C2,Sheet3!$A$2:$A$8,0)[/COLOR],[COLOR=#0000CD]MATCH(VLOOKUP(B2,Sheet2!$A$2:$B$8,2,0),Sheet3!$B$1:$F$1,0)[/COLOR])

That worked! The error was in the red. I was able to fix it. Thanks for the help, you are a life saver!!!

 

[Scott T]

Glad to help.