More Than VLOOKUP()

[Gary''s Student]

I need to be able to lookup values in a table that may include errors. For
example in A1 thru B9 I have:

1 Larry
#DIV/0! Rocky
2 Moe
3 Curley
Shep
#N/A Bullwinkle
happiness Boris
#NAME? Natasha
#NUM! Dudley


If J1 contains happiness, then:

=VLOOKUP(J1,A1:B9,2,False)
correctly displays:
Boris

If I enter =0/0 in J1, both J1 and the formula display #DIV/0!.
I want the formula to display:
Rocky

If I put nothing in J1, I want the function to display:
Shep

This is an easy task for a UDF, but I need a non-VBA solution.

Thanks.

 

[Teethless mama]

=INDEX(B1:B9,MATCH(TRUE,ERROR.TYPE(A1:A9)=ERROR.TYPE(J1),0))

ctrl+shift+enter, not just enter

 

[T. Valko]

Lightly tested...

Array entered** :

=IF(ISERROR(J1),INDEX(B1:B9,MATCH(TRUE,ERROR.TYPE(A1:A9)=ERROR.TYPE(J1),0)),VLOOKUP(J1&"",A1:B9&"",2,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Pete_UK]

I picked out the table entries which had errors in column A and put
them lower down starting in A12, like this:

#DIV0! Rocky 2
#N/A Bullwinkle 7
#NAME? Natasha 5
#NUM! Dudley 6

where the numbers are the error values for those errors. Then this
seemed to worked:

=IF(ISERROR(J1),INDEX(B12:B15,MATCH(ERROR.TYPE(J1),C12:C15,0)),IF
(J1="",B5,VLOOKUP(J1,A1:B9,2,FALSE)))

though, like Biff, I haven't tested it thoroughly.

Hope this helps.

Pete

 

[Gary''s Student]

First, thank you.
It does catch some of the errors.

It does find:
Rocky
Bullwinkle
Dudley

for inputs of:
#DIV/0!
#N/A
#NUM!

It does not work for simple non-error values like 1,2,3
It does not work for blanks
It does not work for #NAME?

I think I may be able to expand on your idea.

Thanks again.

 

[Bernard Liengme]

I played around with ERROR.TYPE which (fittingly!) has some 'errors' of its
own (see http://support.microsoft.com/kb/213891)

In C1 enter this formula
=IF(ISERROR(A1),CHOOSE(ERROR.TYPE(A1),"e1","e2","e3","e4","e5","e6","e7"),A1)
and copy down the column
I choose e1, e2 etc but you can make up any text you like (or a range of
number that you know will never occur in column A)

As you have indicated, enter the lookup value in J1
In J2 use
=IF(ISERROR(ERROR.TYPE(J1)),I1,CHOOSE(ERROR.TYPE(J1),"e1","e2","e3","e4","e5","e6","e7"))

In J3 I used =INDEX(B1:B9,MATCH(J2,C1:C9,0))
I suppose I could have inserted a new column A with the
=IF(ISERROR(B1)...... and then in J3 I could use a VLOOKUP

This works with the following in J1:
numbers 1,2,3 returns the corresponding name from B
=0/0 -- returns Rocky
=X -- returns Natasha since there is no range called X (no way to get
Dudely)
=NA() -- returns Bullwinkle

NOTE: while developing the worksheet and now whenever I enter an error
value, I get an error message "User-defined function not defined" but I just
OK out of that.

best wishes

 

[Gary''s Student]

Thanks! Except for Natasha, the formula is perfect!

 

[Gary''s Student]

Thanks Pete
--
Gary''s Student - gsnu200909

I picked out the table entries which had errors in column A and put
them lower down starting in A12, like this:

#DIV0! Rocky 2
#N/A Bullwinkle 7
#NAME? Natasha 5
#NUM! Dudley 6

where the numbers are the error values for those errors. Then this
seemed to worked:

=IF(ISERROR(J1),INDEX(B12:B15,MATCH(ERROR.TYPE(J1),C12:C15,0)),IF
(J1="",B5,VLOOKUP(J1,A1:B9,2,FALSE)))

though, like Biff, I haven't tested it thoroughly.

Hope this helps.

Pete



.

 

[Gary''s Student]

Thanks Bernard
--
Gary''s Student - gsnu200909

I played around with ERROR.TYPE which (fittingly!) has some 'errors' of its
own (see http://support.microsoft.com/kb/213891)

In C1 enter this formula
=IF(ISERROR(A1),CHOOSE(ERROR.TYPE(A1),"e1","e2","e3","e4","e5","e6","e7"),A1)
and copy down the column
I choose e1, e2 etc but you can make up any text you like (or a range of
number that you know will never occur in column A)

As you have indicated, enter the lookup value in J1
In J2 use
=IF(ISERROR(ERROR.TYPE(J1)),I1,CHOOSE(ERROR.TYPE(J1),"e1","e2","e3","e4","e5","e6","e7"))

In J3 I used =INDEX(B1:B9,MATCH(J2,C1:C9,0))
I suppose I could have inserted a new column A with the
=IF(ISERROR(B1)...... and then in J3 I could use a VLOOKUP

This works with the following in J1:
numbers 1,2,3 returns the corresponding name from B
=0/0 -- returns Rocky
=X -- returns Natasha since there is no range called X (no way to get
Dudely)
=NA() -- returns Bullwinkle

NOTE: while developing the worksheet and now whenever I enter an error
value, I get an error message "User-defined function not defined" but I just
OK out of that.

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP



.

 

[T. Valko]

Except for Natasha, the formula is perfect!

Hmmm....

When I manually enter #NAME? in J1 I get the result Natasha.

If I enter a formula in J1 that returns #NAME?, =SUM(goo), I get the result
Natasha.

?????

 

[Pete_UK]

You're welcome. Having a separate table for errors makes the problem
easier to handle.

Pete