vlookup to a a non continuous range

[velotrout]

I am sure I saw a similar post, and pretty sure this cant be done, but hoping some one has worked out a workaround. Small example:

100-110 = a
114 = b
120-130 = c

Unfortunately I have a table where the gaps in numbers are some times 800 long and I dont want to be pasting 800 values to make gap continoius. The outcome I need is:

if the cell entry is between 100 to 110, I want to returm a value of a
if the cell entry is 113, I want to return an error or #na
if the cell etnry is between 120-130, I want to return a cell value of c in another cell

[mikeTRON]

Welcome to the forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[TMS]

Short story: no, you can't do that. VLOOKUP takes a fourth parameter; this is FALSE if you want an EXACT match and TRUE if you want an inexact match. For the first option, you would need every entry that was valid with the appropriate return value. Any entries not present would return #N/A. For the second option, you would just list, in ascending order, the upper values in a range together with the return values for the range. But that won't give you #N/A for "missing" entries because there won't be any missing entries.

You could probably do it with a User Defined Function (UDF) but that's a different question.

[Tony Valko]

100-110 = a
114 = b
120-130 = c

if the cell entry is between 100 to 110, I want to returm a value of a
if the cell entry is 113, I want to return an error or #na
if the cell etnry is between 120-130, I want to return a cell value of c in another cell

You can use nested If functions...

=IF(AND(A1>=100,A1<=110),"A",IF(A1=114,"B",IF(AND(A1>=120,A1<=130),"C",#N/A)))