Discussion Locked

Fine Tuning VLOOKUP in Excel

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm feeling dumb at the moment because I can't figure this out!!

I am using VLOOKUP to pull back information from a linked spreadsheet and for the most part it works. My problem is that if the value being searched for isn't in the list, it brings back something close. I think that is the way it is supposed to work, but I?d like it to give an error or a zero or something if the exact match isn?t found. Can VLOOKUP perform an exact search and if it doesn?t find an exact match, can it return some sort of error or "not found" option? The exact syntax I'm using is below if that helps:

=IF(B25>0,(VLOOKUP(B25,'E:\My Documents[Pricing Lists.xls]Mat List'!A9:F2122,5)),"n/a")

Basically, if cell B25 isn't empty, it is supposed to look up whatever is in cell B25 and bring back the value in column 6 for that item from the linked workbook. The "n/a" is so is displays n/a until something is typed in B25. If I put B-110 for instance (a mat style in this case) in cell B25, it works fine because B-110 is in the list. If I put something like B-114 in cell B25, it pulls back whatever is closest, because B-114 isn't in the list. I want it to give an error or a zero or something if the exact match isn?t found. I played around with the MATCH function, but I'm not doing something right or MATCH and VLOOKUP won?t work together because it isn't working. Any suggestions would be greatly appreciated!!! :) Thank you!

3 replies

by jimbb

You have 4 parts in the xLOOKUP syntax:
-Where to look (B25)
-The range to look in ('E:\...A9:F2122)
-The column to retrieve the data from (5)
-TRUE/FALSE, that indicates if you want an exact match or not. The 4th option is not required, and ifit's not there it's the same as "TRUE". "FALSE" will force an exact match.

Good luck.

Jim

by dfowler

Jim - thanks for the reply - I tried leaving off the false criteria as you suggested, but it didn't change anything. The reason the false criteria was in place was to eliminate the cell from saying False if nothing is entered at all. However, evenif I remove that criteria and enter a value to lookup that I know isn't on the list, I still get erroneous data instead of a forced match. Since you took the time to reply, I'm allocating you the points anyway and will keep looking for an answer. VBA code may be the final answer, while not my favorite choice. Anyway, Thanks!

This discussion is closed to new replies.