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

	A	B	C	D	E	F	G	H	I	J	K
1	DATE SHIPPED	CUSTOMER	TYPE OF FREIGHT	ITEMS DELIVERED: COMMENTS/DETAILS	QTY	UNIT OF MEASURE	LENGTH (FT)	WIDTH (FT)	SQ. FT. CALC.	RATE	COST
2	1/17/2017	CUSTOMER A	TYPE A	ABC	1.00	ea
3	1/18/2017	CUSTOMER B	TYPE B	DEF	1.00	ea
4	1/19/2017	CUSTOMER C	TYPE C	GHI	1.00	ea
5	1/20/2017	CUSTOMER D	TYPE D	JKL	1.00	ea
6	1/21/2017	CUSTOMER E	TYPE E	MNO	1.00	Sq Ft
7	1/22/2017	CUSTOMER F	TYPE F	PQR	1.00	Sq Ft
8	1/23/2017	CUSTOMER G	TYPE G	STU	1.00	Sq Ft

<colgroup><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

TAB 2 - CUSTOMERS

	A	B	C	D	E	F	G
1	Customer	RATE TYPE	Address	Phone	Email	Attachments	Notes
2	CUSTOMER A	RATE 1	555, FAKE LANE, WA 55555	555-555-5555	ABC@ABC.COM
3	CUSTOMER B	RATE 2	123, FAKE LANE, WA 55555	555-555-5556	ABC@ABC.COM
4	CUSTOMER C	RATE 3	777, FAKE LANE, WA 55555	555-555-5557	ABC@ABC.COM
5	CUSTOMER D	RATE 4	888, FAKE LANE, WA 55555	555-555-5558	ABC@ABC.COM
6	CUSTOMER E	RATE 5	999, FAKE LANE, WA 55555	555-555-5559	ABC@ABC.COM
7	CUSTOMER F	RATE 1	111, FAKE LANE, WA 55555	555-555-5510	ABC@ABC.COM
8	CUSTOMER G	RATE 1	222, FAKE LANE, WA 55555	555-555-5511	ABC@ABC.COM

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

TAB 3 - RATES

	A	B	C	D	E	F	G
1	Freight Type	RATE 1	RATE 2	RATE 3	RATE 4	RATE 5	Unit of Measure
2	TYPE A	$15.00	$20.00	$20.00	$25.00	$40.00	ea
3	TYPE B	$35.00	$40.00	$40.00	$45.00	$70.00	ea
4	TYPE C	$35.00	$40.00	$40.00	$45.00	$70.00	ea
5	TYPE D	$15.00	$20.00	$20.00	$25.00	$40.00	ea
6	TYPE E	$1.65	$1.85	$1.85	$2.65	$8.60	Sq Ft
7	TYPE F	$1.65	$1.85	$1.85	$2.65	$8.60	Sq Ft
8	TYPE G	$1.85	$2.00	$2.00	$2.65	$8.60	Sq Ft

<colgroup><col><col span="5"><col></colgroup><tbody>
</tbody>

 

[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.