Lookup
 

I created a lookup formula - LOOKUP(C:C,'UPIN INDEX'!A:A,'UPIN INDEX'!B:B),
the formula works as desired, except when the number being looked up doesn't
exist it doesn't come back with FALSE, it fills the blank with the number and
name just preceding. How do I change this?

If A:B on UPIN INDEX is sorted in ascending order and can be kept as
such, try:

=IF(LOOKUP(C2,'UPIN INDEX'!$A$2:$A$1500)=C2,LOOKUP(C2,'UPIN
INDEX'!$A$2:$B$1500),"")

Otherwise, try:

=VLOOKUP(C2,'UPIN INDEX'!A:B,2,0)

Jeanette wrote:
I created a lookup formula - LOOKUP(C:C,'UPIN INDEX'!A:A,'UPIN INDEX'!B:B),
the formula works as desired, except when the number being looked up doesn't
exist it doesn't come back with FALSE, it fills the blank with the number and
name just preceding. How do I change this?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Worked like a DREAM ;-)

"Aladin Akyurek" wrote:

If A:B on UPIN INDEX is sorted in ascending order and can be kept as
such, try:

=IF(LOOKUP(C2,'UPIN INDEX'!$A$2:$A$1500)=C2,LOOKUP(C2,'UPIN
INDEX'!$A$2:$B$1500),"")

Otherwise, try:

=VLOOKUP(C2,'UPIN INDEX'!A:B,2,0)

Jeanette wrote:
I created a lookup formula - LOOKUP(C:C,'UPIN INDEX'!A:A,'UPIN INDEX'!B:B),
the formula works as desired, except when the number being looked up doesn't
exist it doesn't come back with FALSE, it fills the blank with the number and
name just preceding. How do I change this?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Glad to help. Just a note: When A:B is sorted on A, the LOOKUP formula
is way faster than the VLOOKUP formula with the match-type set to 0.
That's why I also suggested a LOOKUP formula wrt your previous question
instead of an IF formula with at least 3 functions...

Jeanette wrote:
Worked like a DREAM ;-)

"Aladin Akyurek" wrote:


If A:B on UPIN INDEX is sorted in ascending order and can be kept as
such, try:

=IF(LOOKUP(C2,'UPIN INDEX'!$A$2:$A$1500)=C2,LOOKUP(C2,'UPIN
INDEX'!$A$2:$B$1500),"")

Otherwise, try:

=VLOOKUP(C2,'UPIN INDEX'!A:B,2,0)

Jeanette wrote:

I created a lookup formula - LOOKUP(C:C,'UPIN INDEX'!A:A,'UPIN INDEX'!B:B),
the formula works as desired, except when the number being looked up doesn't
exist it doesn't come back with FALSE, it fills the blank with the number and
name just preceding. How do I change this?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.