VLookup Syntax Assistance Please

[DougStroud]

I am setting up a table for shipping costs on sheet 2. I want to pull the cost over when it matches the item and method of shipping.
Item is Shoes, Method is Ground, Cost is $8.00.

Sheet2 Organization is:

A1= Item
B1= Method
C1= Cost

Values:
A2= Shoes
B2= Ground
C2= $8.00

My formula is:
=VLOOKUP(A2:A10,Table!C2:C10,3,true)

I receive a #Value error.

thanks,

Doug

 

[DougStroud]

Aladin,

I have not solved this as of yet. Here are two sheets, worksheet and table.

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Day	Date	Order Taken by	Customer	Manufacturer	Item Photo	Item	Size	Color	Item Cost	w/ Tax	Shipping Method	Shipping Cost	Total Purchase Price	Deposit Amount	Amount Due	Date Ordered	Who Ordered	CSR Accepting Order	Reference#	Date Scheduled to Ship	Received Date	Customer Notified
2	Sunday	9/25	Doug	Spencer Brown	es'		Accel Plus	9 1/2	Gray/Black	$57.99	$60.89	Ground	#N/A	#N/A	$0.00	#N/A
Sheet1

Book1.xls
	A	B	C	D	E	F	G
1	Concat	Item	Method	Cost
2	Shoes#Ground	Shoes	Ground	$8.00		Shoes	Ground
Table

Formula:
=VLOOKUP(F2&"#"&G2,Table!$A$2:$D$2,4,0)

residing in cell M2 on Sheet1

 

[PaddyD]

Doug,

Haven't looked at the details, but from your formula:

=VLOOKUP(F2&"#"&G2,Table!$A$2:$D$2,4,0)

...it's clear you're still not quite there with how vlookup() works. I posted a step-by-step in this post:

http://www.mrexcel.com/board2/viewtopic.php?t=62709

...which might help.

 

[PA HS Teacher]

Is there a #Accel Plus entry in your table?

I notice F2 is blank

 

[Aladin Akyurek]

Aladin,

I have not solved this as of yet. Here are two sheets, worksheet and table.

...

Formula:
=VLOOKUP(F2&"#"&G2,Table!$A$2:$D$2,4,0)

residing in cell M2 on Sheet1

The Table Sheet

Book11
	A	B	C	D	E
1	Concat	Item	Method	Cost
2	Shoes#Ground	Shoes	Ground	$8.00
3	Accel Plus#Ground	Accel Plus	Ground	$7.80
4	Shoes#Air	Shoes	Air	$18.65
5
Table

A2, copied down:

=B2&"#"&C2

Book11
	C	D	E	F	G	H	I	J	K	L	M
1	Order Taken by	Customer	Manufacturer	Item Photo	Item	Size	Color	Item Cost	w/ Tax	Shipping Method	Shipping Cost
2	Doug	Spencer Brown	es'		Accel Plus	9 1/2	Gray/Black	$57.99	$60.89	Ground	$7.80
3	Jon	Spencer Brown	qw		Shoes	42	Black	$124.88		Ground	$8.00
4
Sheet1

M2, copied down:

=VLOOKUP(G2&"#"&L2,Table!$A$2:$D$4,4,0)

 

[DougStroud]

Aladin, Yee Haw!
Following your example I got it to work on my end.
What is the # doing here for the result to work?

Thanks for you assistance.

Doug

 

[Aladin Akyurek]

Aladin, Yee Haw!
I following this I got it to work on my end.
What is the # doing here for the result to work?

Thanks for you assistance.

Doug

Works like a separator...

 

[DougStroud]

Aladin Great, thanks- really. Your assistance has been especially helpful.

Have g-day.

Doug

 

[MikeNewton]

I think you'll find your formula should be :

=VLOOKUP(A2,Table!A2:C10,3,TRUE)

Regards

Mike

 

[DougStroud]

PaddyD,
Got it now. I did not get the concept that the lookup started with the value in the current worksheet. I was looking to the table initially. I would not have gotten how to look for two values had Aladin not shown me how to combine two values on the table.
With the reference link you provided, which explains the VLookup very clearly and Aladin's solving the 2nd part I have gotten the concept down better now.


Thank you,


Doug