# vlookup with multiple matches - which match is returned

#### West Man

##### Well-known Member
With a formula like VLOOKUP(Z2,A2:B100,2,0) where A2:A100 is sorted ascending and there are multiple matches, it appears, with very limited testing, that the match is always the first in the range. Is this always true? I would think that a binary search would be occurring which may not always result in the first matching value being found.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Prense01

##### Board Regular
The Range Look Up type 0 forces an exact match. The first exact match found in a top to bottom query will be the match.

Edit: There are ways to return mutiple results...

#### West Man

##### MrExcel MVP
With a formula like VLOOKUP(Z2,A2:B100,2,0) where A2:A100 is sorted ascending and there are multiple matches, it appears, with very limited testing, that the match is always the first in the range. Is this always true? I would think that a binary search would be occurring which may not always result in the first matching value being found.

Binary search occurs when match-type is 1, not 0. And, with the former the last occurrence of the lookup value will be picked out.

#### West Man

##### Well-known Member

Thank you Aladin. Of course it is match type 1. I don't know what I was thinking. However I believe I understand how a binary search works but fail to understand why with a binary search the last match is always found.

#### Prense01

##### Board Regular
Thank you Aladin. Of course it is match type 1. I don't know what I was thinking. However I believe I understand how a binary search works but fail to understand why with a binary search the last match is always found.

My understanding is because it finds the next highest value and falls back one. Isn't this the reason the table has to be sorted ascending?

##### MrExcel MVP

My understanding is because it finds the next highest value and falls back one. Isn't this the reason the table has to be sorted ascending?

Thank you Aladin. Of course it is match type 1. I don't know what I was thinking. However I believe I understand how a binary search works but fail to understand why with a binary search the last match is always found.

Here is an effort...

In what follows, LV stands for 'lookup value' and three situations are taken up. The end results can all be obtained with either a LOOKUP formula or INDEX/MATCH (also VLOOKUP) formula in which the value of the match-type is set to 1.

1.) Divide the search array (the first column of the table) in 2 halves (lower half colored as gold, upper half as turquoise).

2.) LV is compared with X, the first value of the upper half: LV >= X ? If this evaluates to Yes, the new search array becomes the upper half, otherwise the lower half.

3.) Repeat until there is nothing to divide into halves.

Situation I is a regular case.
Situation II shows why we get Ved as result.
Situation III shows why we get 2 (Cad) when we invoke:

=LOOKUP(9.99999999999999E+307,Range)

where 9.99999999999999E+307 is the same as 1.00E+308.

Although not sure, I hope this description is consistent with how Match/Vlookup, Hlookup with match-type set to 1 and Lookup behave.

Last edited:

#### West Man

##### Well-known Member
Thanks to each of you. it is now clear

Replies
10
Views
154
Replies
2
Views
146
Replies
2
Views
227
Replies
17
Views
255
Replies
9
Views
76

1,127,252
Messages
5,623,624
Members
415,981
Latest member
Baltwin

### 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