I am doing a vlookup in Excel 2016. If my input value is not in the list, I want to report an error (rather than going to the closest match). How would I do that?
thanks, Randy
I am doing a vlookup in Excel 2016. If my input value is not in the list, I want to report an error (rather than going to the closest match). How would I do that?
thanks, Randy
Here is a generic example
=VLOOKUP(LOOKUP VALUE,TABLE ARRAY,COLUMN INDEX NUMBER, FALSE) the FALSE will force the VLOOKUP to search for an EXACT match. If an exact match isn't found, a #N/A error is returned.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Not sure what you wrote but this should do it for you... =VLOOKUP(A2,lookup range,#columns over,false)
Should return #N/A if the value isn't there.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
@RandWald
Change
VLOOKUP(X,Table,N)
or
VLOOKUP(X,Table,N,TRUE)
or
VLOOKUP(X,Table,N,1)
that you might have, to just:
VLOOKUP(X,Table,N,0)
use index match
Try this...
Data Range
A B C D E 1 2 5 Poor 17 #N/A 3 10 Average 4 15 Good 5 20 Excellent 6 ------ ------ ------ ------ ------
This formula entered in E2:
=VLOOKUP(D2,A2:B5,2,0)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks