VLookup Syntax Assistance Please

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 25, 2005

• #2

Doug

The first argument of a VLOOKUP should be a single value not a range.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 25, 2005

• #3

Doug,
you are trying to find a matrix instead of a single value
A2:A10 should be A2

kind regards,
Erik

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,968

• Sep 25, 2005

• #4

If I am looking for the item and the method to pull the price, do I place a text string in the first criteria of the formula? I tried placing a cell reference of A2 and that returns a #Ref error. If I place the text string of Shoes I get #name error.

I am still not very good with the Vlookup function, your assistance is appreciated.

Doug

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 25, 2005

• #5

DougStroud said:

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

Click to expand...

Book11
	A	B	C	D	E	F	G	H
1	Concat	Item	Method	Cost
2	Shoes#Ground	Shoes	Ground	$8.00		Shoes	Ground	8
Sheet2

A2, copied down:

=B2&"#"&C2

H2:

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

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 25, 2005

• #6

sorry, Doug,

I didn't see the second error while the first was so obvious
the table is from column A to C
so you need
=VLOOKUP(A2,Table!A2:C10,3,true)

this wil work
Erik

EDIT: unless I missed a part of the question which Aladin didn't

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,968

• Sep 25, 2005

• #7

Erik,
Ain't happenin'. I am leaving work now and will get home and use html to post it up so you can see it, if you do not mind.

Doug

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,968

• Sep 25, 2005

• #8

Oh, hey Aladin.
I did not see your post. I will go over what you have mocked up, thanks.

BTW: Did you get my Private message a couple of days ago regarding the exporting of a worksheet with foreign language characters?

Doug

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 25, 2005

• #9

DougStroud said:

Oh, hey Aladin.
I did not see your post. I will go over what you have mocked up, thanks.

Click to expand...

BTW: Did you get my Private message a couple of days ago regarding the exporting of a worksheet with foreign language characters?
...

Click to expand...

I've probably missed the PM, but I don't know how to tackle that question.

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,968

• Sep 25, 2005

• #10

Erik,
Ok so here is the html version.

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	#REF!	#REF!	$0.00	#REF!
Sheet1

Table

Book1.xls
	A	B	C	D
1	Item	Method	Cost
2	Shoes	Ground	$8.00
Table

Cell M2 is where the VLookup formula resides and I receive the #Ref error.

Here is the formula.
=VLOOKUP(A2,Table!C2:C10,3,TRUE)

 

Upvote 0