# need a better lookup function

#### vacation

##### Board Regular
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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
=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.

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.

vacation said:
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?

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

vacation said:
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.

Replies
10
Views
244
Replies
8
Views
255
Replies
0
Views
275
Replies
4
Views
184
Replies
2
Views
202

1,207,436
Messages
6,078,546
Members
446,346
Latest member
shinbum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back