need a better lookup function

[vacation]

Hi,

I have been using the function:
=LOOKUP(B10,ListSheet!$C$2:$C$29763,ListSheet!$D$2:$D$29763)
To lookup corresponding values.


But the problems with "LOOKUP" function are:
a. It does not always do an exact match.
b. It finds the nearest match which is not always the correct answer.
c. The "$C$2:$C$29763" list needs to be sorted.


So how can I do a lookup where:
1. Only the exact match returns a result.
2. Actually, the first exact match returns a result. There will be duplicates in the "$C$2:$C$29763" list.
3. If there is no exact match then it should return some error/warning message.
4. The "$C$2:$C$29763" list does not need to be sorted.


Is there some other fuction which will do this?


Thanks.

 

[Aladin Akyurek]

=VLOOKUP(B10,ListSheet!$C$2:$D$29763,2,0)

You'll get #N/A if B10 is not available in $C$2:$C$29763. Moreover, you'll always retrieve the value associated with the first occurrence of B10 in the traget table.

 

[vacation]

Thank you Aladin Akyurek.

 

[vacation]

Hi,

This function
=VLOOKUP(B10,ListSheet!$C$2:$D$29763,2,0)
works for me in every scenario except one:

Assume that the above list range contains multiple occurences of the value contained in B10.
The first occurence has the corresponding value of 0.
The next occurence has the corresponing value of -3.
The next occurence has the corresponing value of -1.
The next occurence has the corresponing value of 4.

So I would like the vlookup to return a 4 but it returned a 0.
How can this vlookup function be forced to return the highest value?
In the above example it would return a 4 (or the highest number), which is what I really need.

Thanks.

 

[Aladin Akyurek]

Hi,

This function
=VLOOKUP(B10,ListSheet!$C$2:$D$29763,2,0)
works for me in every scenario except one:

Assume that the above list range contains multiple occurences of the value contained in B10.
The first occurence has the corresponding value of 0.
The next occurence has the corresponing value of -3.
The next occurence has the corresponing value of -1.
The next occurence has the corresponing value of 4.

So I would like the vlookup to return a 4 but it returned a 0.
How can this vlookup function be forced to return the highest value?
In the above example it would return a 4 (or the highest number), which is what I really need.

Thanks.

Now you're in touble. You'll be forced to apply an expensive formula. To how many cells did you copy the current formula, 10, 100, 1000?

 

[vacation]

I copied this formula to 38,000 cells.
So far the performance has been good.

 

[Aladin Akyurek]

I copied this formula to 38,000 cells.
So far the performance has been good.

Now, you're going to suffer:

=INDEX(ListSheet!$C$2:$D$29763,SMALL(IF(ListSheet!$C$2:$D$29763=B10,ROW(ListSheet!$C$2:$D$29763)-ROW(ListSheet!$C$2)+1,ROW($C$29763)+1),COUNTIF(ListSheet!$C$2:$C$29763,B10)),2)

which must be confirmed with control+shift+enter instead of just enter. The formula is from Chip Pearson's site. It retrieves the last value associated with the lookup value.