Lookup value not present. 1

[dschomburg]

I have a formula;

=LOOKUP($B$25,Q_RoutedToPercent.xls!$B$2:$B$17,Q_RoutedToPercent.xls!$C$2:$C$17)

But sometimes, whatever I'm looking up is not present. How can I include a sort of "IF this lookup is not there return a blank" kind of function?


Any help would help a heap.

Dave

 

[SimonDavis]

The syntax should be =if(isblank(yourlookupformula)=true," ",yourlookupformula)

If that doesn't work, try a few of the other IS functions - it depends on what data you are using.

Also, check the syntax for the if formula, I use non-English software, and it sometimes has differences.

 

[Databaseguy]

=IF(ISNA(LOOKUP($B$25,Q_RoutedToPercent.xls!$B$2:$B$17,Q_RoutedToPercent.xls!$C$2:$C$17),Something, VLOOKUP(B2,$C$1:$C$11,1,0)),0,VLOOKUP(B2,$C$1:$C$11,1,0))

 

[DaleWatson123321]

Hi Dave,

The first two responses should also work, but having prepared this option, I thought to still send it.

I tested this and it works. Notice that the syntax is slightly different than yours, in that the reference to the lookup range is defined TOGETHER ...i.e. $B$2:$C$17.

Formula...

=IF(LOOKUP($B$25,[Q_RoutedToPercent.xls]Sheet1!$B$2:$C$17)=0,"",LOOKUP($B$25,[Q_RoutedToPercent.xls]Sheet1!$B$2:$C$17))

Hope this also helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dschomburg]

Hey folks,
Thanks for all your responses. But alas, I'm still having issues.
With all 3 examples the value returned is equal to the Row before it.
I'm wondering if there is a limit to number of characters the lookup function checks?

There are 3 rows with similar values to lookup;

LEC Ops Collection 11
LEC/Delinq Lease 27
LEC/SALES Lease 129

The value I'm having issues with is ="LEC/DSP" which is not in todays data. The lookup function is returning the number '27' for both "LEC/Delinq Lease" and "LEC/DSP" (which is not there).

Does this make sense?

Dave

 

[DaleWatson123321]

Hi Dave,

Finally getting back to you on this - WITH THE PROPER SOLUTION.

My initial response was based on an extention of your formula which used the "LOOKUP" function. I simply added the "IF" function and collapsed the coordinate range. I recently checked your latest response, and confirmed that your formula does NOT work.

However, JVFriederick recently posted a response to another problem where he corrected me on my use of the VLOOKUP function. (That post was titled "Excel: compare lists" - initially posted Aug 13th.) I had been used to the "old" version of the (Lotus 123) VLOOKUP function which did not have the "TRUE/FALSE" argument.

With this added "TRUE" or "FALSE" argument, using "FALSE" will cause the VLOOKUP function to find an exact match in a table. I tested the following revised formula, and it works with your latest data.
=IF(VLOOKUP($B$25,Q_RoutedToPercent.xls!table,2,FALSE)=0,"",VLOOKUP($B$25,Q_RoutedToPercent.xls!table,2,FALSE))

In the above formula, I have used the range name "table" in place of your coordinates. I highly recommend you begin to use Ranges Names liberally, as they are HIGHLY useful in referencing ranges and cells for use in formulas, for navigation around the workbook, and for reference in VBA code. (To assign a Range Name, simply highlight the cell or range or cells, then use Control-F3, type the name you want to assign, and then hit Enter.) To simply "check out" the name, you can use the "GoTo" function key - F-5 - followed by the name, and Enter.

The second argument (2) is the "column offset". Because your table has 2 columns, you want to use "2" which refers to the second column which contains the values.

Let me know how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dschomburg]

Thanks again for looking into this. What I ended up doing is using 2 additional columns on the main page (and hiding them) in the first one, I placed the lookup function which returns either the correct text or not then in the second new column I just checked to see if the Value I was looking up matched the value returned and Place "yes" or "no" in the 2nd new column.

Then in the cell where I actually want the numeric value stored I placed the if statment first checking the 2nd new column - if it equals "no" (meaning they are not the same text value) then just return " " but if "yes" then repeat the lookup function and return the desired cell.

 

[sgreenwood]

Both SimonDavis and Databaseguy's solutions will work, but unless you add ",false" to the end of the lookup formula it will always give you the previous cell if the lookup isn't found. Using the ",false" tells it to ONLY give you the data if the lookup is found.

Hope that helps