Modified Lookup

[drgkt]

Hi
I would like help in a function that would look up numbers or numbers entered as text in f2:j11 and return the value in column A, if exact match or "Unknown" if not found.

Also how to modify, if numbers were not in consecutive columns.

Thanks

[bebo021999]

With F14 is lookup number

=INDEX(A:A,MAX(IF($F$2:$J$11=F14,ROW($F$2:$J$11))))

[drgkt]

I get #value! (A value used in the formula is of the wrong data type).


phone.jpg

[Glenn Kennedy]

It works fine. However, Bebo forgot to tell you that it is an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[drgkt]

Indeed.
Thank you!

For a non found number I get A1 = (Name). How can I get "Uknown"?

Also, what if the data were not in consecutive columns?

[Czeslaw]

Non array formula.

Please Login or Register  to view this content.

[Glenn Kennedy]

OK. Another array formula, for the first part:

=IFERROR(INDEX($A$2:$A$11,MAX(IF($F$2:$J$11=F14,ROW($F$2:$J$11)))-ROW($1:1)),"Unknown")

[Tony Valko]

Assuming you're using Excel 2002 as your profile indicates.

Try this array formula**:

=IF(MAX(IF($F$2:$J$11=F14,ROW($F$2:$J$11)))=0,"Uknown",INDEX(A:A,MAX(IF($F$2:$J$11=F14,ROW($F$2:$J$11)))))

** 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.

[Glenn Kennedy]

Thanks, Tony, I just spotted that....

If you break up the range with text, it'll still work.

[drgkt]

Strange I did not get any notifications about new posts. I let the webmaster know.

Thank you all

As to Czeslaw's proposed answer I get: #NAME?

[Czeslaw]

Please Your example with my formula.

[drgkt]

Sorry, I get #NAME? if not found !

Please modify to get "Unknown"

[Czeslaw]

Please.
I changed.

[drgkt]

Thank you all.

I just wanted you to know that if the number is entered as text it will be missed.
Just type "50 in J11 and 50 in F14.

[drgkt]

The non array formula will give erroneous results if it is used with reference to another workbook.