More Than VLOOKUP()
 

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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

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

ctrl+shift+enter, not just enter


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

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.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

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

On Nov 27, 4:29*pm, Gary''s Student
wrote:
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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

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.
--
Gary''s Student - gsnu200909


"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

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","e 3","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(J 1),"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

"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200909

More Than VLOOKUP()
 

Thanks! Except for Natasha, the formula is perfect!
--
Gary''s Student - gsnu200909


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200909


.

More Than VLOOKUP()
 

Thanks Pete
--
Gary''s Student - gsnu200909


"Pete_UK" wrote:

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

On Nov 27, 4:29 pm, Gary''s Student
wrote:
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.
--
Gary''s Student - gsnu200909

.

More Than VLOOKUP()
 

Thanks Bernard
--
Gary''s Student - gsnu200909


"Bernard Liengme" wrote:

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","e 3","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(J 1),"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

"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200909

.

More Than VLOOKUP()
 

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.

?????

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
Thanks! Except for Natasha, the formula is perfect!
--
Gary''s Student - gsnu200909


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in
message
...
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.
--
Gary''s Student - gsnu200909


.

More Than VLOOKUP()
 

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

Pete

On Nov 27, 6:40*pm, Gary''s Student
wrote:
Thanks Pete
--
Gary''s Student - gsnu200909



"Pete_UK" wrote:
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

On Nov 27, 4:29 pm, Gary''s Student
wrote:
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.
--
Gary''s Student - gsnu200909

.- Hide quoted text -

- Show quoted text -

More Than VLOOKUP()
 

On Nov 28, 12:29*am, Gary''s Student
wrote:
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.
--
Gary''s Student - gsnu200909

Array formula: should capture all ur needs
=INDEX(B9:B17,IF(ISERROR(D8),MATCH(TRUE,ERROR.TYPE (A9:A17)=ERROR.TYPE
(D8),0),IF(ISBLANK(D8),MATCH(TRUE,ISBLANK(A9:A17), 0),MATCH
(D8,A9:A17,0))))