VLOOKUP Errors Driving Me Insane :-)

[Alex Caldecott]

HELP!

I'm trying to pull through some prices from one sheet into another using VLOOKUP. It's working for most but some are throwing an error #N/A.

Formula I'm using - =VLOOKUP(B2,Sheet1!A2:C8039,2,FALSE)

A few screenshots attached.

Some of the errors are being thrown because the items are not in the second sheet but most of them are there and just not pulling through.

What am I missing?

 

[Joe4]

You need to lock the row references on your look up range, or else as you copy the formula down, the rows of that range will shift accordingly.
So enter this formula and copy it down:

=VLOOKUP(B2,Sheet1!A$2:C$8039,2,FALSE)

Note the #N/A errors occur when it does not find a match.
You can use the IFERROR formula to address that, if you like, i.e.

=IFERROR(VLOOKUP(B2,Sheet1!A$2:C$8039,2,FALSE),"No Match")

 

[Alex Caldecott]

Thank you SO much!!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Peter_SSs]

A few other things to consider.

• Firstly, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. We cannot copy from pictures to test.
  (If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)
  
  
• Since this formula is only looking up in columns A:B of Sheet1, you are best to only have those two columns as the lookup range in your lookup formula. The reason is that if A:C is referenced like you have and a value in column C of Sheet1 is changed, that will cause all the lookup formulas to recalculate even though there is no possibility of that recalculation producing any different results from the formulas. It may not be crucially important if your worksheet is not too big, but it makes sense to me to be efficient in that regard.
  
  
• Since the VLOOKUP returns a #N/A error if the lookup value is not found, it also makes sense to me to just target that error with the formula. That way you wouldn't accidentally hide some other sort of error if it occurred. So I would suggest IFNA() rather than IFERROR()
  
  
• It looks like you data might be quite big and since you have Excel 365, instead of copying the formula down hundreds of rows, you can get all the hundreds (I'm guessing the number) of results from a single formula in the top cell.
  
  
• Finally, again since you have 365, XLOOKUP could be considered as you do not have to trap an "error" at all because that function has an optional argument for what to return if the lookup value is not found.

So to demonstrate my points, in the second sheet below
I have the two formulas from post 2 in columns H:I
The cell J2 formula is reducing the lookup range to the two relevant columns in Sheet1 but it also does not need to be copied down.
Cell K2 is similar but showing that IFNA can be used
The L2 formula also does not need to be copied down and shows how XLOOKUP can provide the same results without any "error" type function.

Alex Caldecott.xlsm
	A	B
1	Product Code	Price
2	Prod Code 286	44.88
3	Prod Code 798	41.49
4	Prod Code 752	64.48
5	Prod Code 186	33.59
6	Prod Code 87	90.62
7	Prod Code 491	27.2
8	Prod Code 995	62.2
9	Prod Code 746	96.83
10	Prod Code 331	59.77
11	Prod Code 993	46.52
12	Prod Code 522	80.3
13
Sheet1

Alex Caldecott.xlsm
	B						H	I	J	K	L
1	Product Code
2	Prod Code 798						41.49	41.49	41.49	41.49	41.49
3	Prod Code 186						33.59	33.59	33.59	33.59	33.59
4	Prod Code 202						#N/A	No Match	#N/A	No Match	No Match
5	Prod Code 746						96.83	96.83	96.83	96.83	96.83
6	Prod Code 817						#N/A	No Match	#N/A	No Match	No Match
7	Prod Code 811						#N/A	No Match	#N/A	No Match	No Match
8	Prod Code 993						46.52	46.52	46.52	46.52	46.52
9	Prod Code 491						27.2	27.2	27.2	27.2	27.2
10	Prod Code 286						44.88	44.88	44.88	44.88	44.88
Worksheet

Cell Formulas
Range	Formula
H2:H10	H2	=VLOOKUP(B2,Sheet1!A$2:C$8039,2,FALSE)
I2:I10	I2	=IFERROR(VLOOKUP(B2,Sheet1!A$2:C$8039,2,FALSE),"No Match")
J2:J10	J2	=VLOOKUP(B2:B10,Sheet1!A$2:B$8039,2,0)
K2:K10	K2	=IFNA(VLOOKUP(B2:B10,Sheet1!A$2:B$8039,2,0),"No Match")
L2:L10	L2	=XLOOKUP(B2:B10,Sheet1!A2:A8039,Sheet1!B2:B8039,"No Match")
Dynamic array formulas.

 

[Alex Caldecott]

Thanks Peter, I appreciate you taking the time to reply!

 

[Peter_SSs]

Thanks Peter, I appreciate you taking the time to reply!

You're welcome. Just wondering if you tried any of the suggestions where you only need to enter the formula in one cell?