Call row number after LOOKUP function

[trijamms]

Hi all,

I am trying to call the row number for a value, but there could be a duplicate, so I want to call the final cell with that value.

Here's the situation. I used this function (=LOOKUP(9.99E+307,B:B)) to get the value of the last cell a with a value in column B. This works fine. The last cell's value comes up just like I expected.

Then, I used the MATCH function to get the row number for the cell. Again, works just fine... Except that on occasion, I can have a duplicate come up in column B. If there's a duplicate, the MATCH function finds the first instance of the value found using LOOKUP and returns the wrong row number (the first instance rather than the last).

Any solutions?

Thanks for your help!

 

[JoeMo]

One way is to use a helper column (col B in the example below) to distinguish between duplicate values (100 in the example below). Then the last of the altered duplicates has a slightly different value to match.

Excel Workbook
	A	B	C	D
1	1	1.000001		8
2	2	2.000002
3	3	3.000003
4	100	100.000004
5	6	6.000005
6	5	5.000006
7	4	4.000007
8	100	100.00001
Sheet8

 

[Jonmo1]

Try just changing Lookup to Match

=MATCH(9.99E+307,B:B)

 

[trijamms]

Awesome! Totally worked Jonmo, thanks!

Can you explain why using that huge number causes Excel to look at the last entered cell? If I understood that, your solution would have been more apparent.

 

[Aladin Akyurek]

Awesome! Totally worked Jonmo, thanks!

Can you explain why using that huge number causes Excel to look at the last entered cell? If I understood that, your solution would have been more apparent.

That huge number is an internal constant of Excel itself (It's not 999 or any other like 2^15, 1E100, 99^99, etc.). LOOKUP and kindred functions which do approximate match lands on the last numeric value in the reference they are given as a result of a binary search algorithm (which is very fast).

See for more:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

 

[Jonmo1]

You're welcome

The Match is doing a 'closest match' lookup, which means it's searching for the largest number that is less than or equal to the lookup value.
The huge number just ensures ALL the numbers in the range will be Less Than the lookup value, therefore it returns the last number.

 

[Aladin Akyurek]

You're welcome

The Match is doing a 'closest match' lookup, which means it's searching for the largest number that is less than or equal to the lookup value.
The huge number just ensures ALL the numbers in the range will be Less Than the lookup value, therefore it returns the last number.

The huge number doesn't ensure anything. It's the way the algorithm underlying LOOKUP, MATCH of closest match, etc. operates ensures the last value numeric result.

 

[trijamms]

Thanks for the help and explanations guys!