VLookup getting rid of #N/A

[4ank]

Hi,
this is my first post so excuse me if it sounds like a newb question.

I used this tip to help me get rid of the #N/A I was getting.

http://www.mrexcel.com/td0110.html

However, now I do NOT want an exact match to my lookup value, is there a way to use this statement without having to find an exact match?

IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")

 

[cheveley]

I generally use the the isna() formula if there is nothing to look up and dont wont the error message returned, but a blank cell.

i.e. =if(isna(vlookup(x,x,x,false)),"",vlookup(x,x,x,false))

 

[4ank]

Well, there are things to lookup, but I wanted to make this a template, and did not want #N/A's all over. Also, I am using it for an automatic download for a fellow employee.

 

[Mark W.]

Hi,
this is my first post so excuse me if it sounds like a newb question.

I used this tip to help me get rid of the #N/A I was getting.

http://www.mrexcel.com/td0110.html

However, now I do NOT want an exact match to my lookup value, is there a way to use this statement without having to find an exact match?

IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")

The only way you can get #N/A with an approximate match VLOOKUP is to search for a value that is less than the 1st entry in the table_array. To alleviate this problem simply set the leftmost column of the 1st table_array record to ="" as shown below. Fix your table not the formula!!!

Book1
	A	B	C	D	E	F	G	H
1
2	cat
3	dog
4	mouse
5
6
7
8
Sheet1

 

[4ank]

Thank you both for your replies. I got it working thanks to you. I appreciate the quick response. This forum seems like a very helpful and useful tool!

Thanks again, I am sure you will hear some more from me.

 

[TWTHOMAS]

The only way you can get #N/A with an approximate match VLOOKUP is to search for a value that is less than the 1st entry in the table_array. To alleviate this problem simply set the leftmost column of the 1st table_array record to ="" as shown below. Fix your table not the formula!!!


<CENTER></CENTER>

This is awesome! MUCH Simpler than any other way I have found.