Finding the Last Occurrence of a specific value in a column

[MarkVMcCullagh]

What is the easiest way to find the last occurrence of a value in a table using functions. I want to avoid VB if at all possible and note the row number and use it in an index function to report text adjacent to that last occurrence. I would normally use match but match only records the first match and not the last.

Thanks in advance

 

[dk]

Hi Mark,

One way of doing this is using an array formula. Array formulas must be entered using Ctrl-Shift-Enter, not just enter.

Say you're searching A1:A20 for the last row containing 11, you could use this formula:-

=MAX(ROW(1:20)*(A1:A20=11))

Obviously, change to suit your needs.

For more info on array formulas take a look at this :-

http://www.emailoffice.com/excel/arrays-bobumlas.html

 

[MarkVMcCullagh]

Thanks Dan

At first I thought because I hadn't told you the full story it wouldn't solve my problem but I worked on it and it turns out that it does with the following formula =MAX(ROW($1:2)*(B$2:B3=C3)) where the rows change as I move down through the table.

 

[covertsonic]

Thanks Dan

At first I thought because I hadn't told you the full story it wouldn't solve my problem but I worked on it and it turns out that it does with the following formula =MAX(ROW($1:2)*(B$2:B3=C3)) where the rows change as I move down through the table.

To remove the confusion of the CTRL + Shift + enter, just put SUMPRODUCT around the formula.
=SUMPRODUCT(MAX(ROW($1:2)*(B$2:B3=C3)))

 

[Jonmo1]

Considering the ultimate goal of extracting a value in adjescent column based on the last occurance..

You can bypass the Middle Step of 'noting' the row number and using it in index..

Try
=LOOKUP(2,1/(B$2:B3=C3),A$2:A3)

Where A$2:A3 is the adjescent column you want to extract a value from.

 

[hippiehacker]

you know that you replied to a thread which is 11 years old?

 

[cheekbones3]

Thanks Dan

At first I thought because I hadn't told you the full story it wouldn't solve my problem but I worked on it and it turns out that it does with the following formula =MAX(ROW($1:2)*(B$2:B3=C3)) where the rows change as I move down through the table.

Sort of works for me, with an additional column. Thanks! And yes, still relevant after all these years.

 

[Vince42]

Hi,

=LOOKUP(2,1/(B$2:B3=C3),A$2:A3)

I just wondered what the parameters "2" and "1/" actually do and whether there are other parameters to achieve the same result.

 

[Agincourt2]

Hi,



I just wondered what the parameters "2" and "1/" actually do and whether there are other parameters to achieve the same result.

Lookup works similar to vlookup(true clause) where it searches for the first match, if it cant find one the formula will back step to the previous valid answer. "2" is the answer the above formula is looking for, "1/" splits the results into 2 groups (1) or (#DIV/0!). As the second group isn't valid and a "2" wont be returned in the results, the formula will return the last (1) or occurrence that is being sought.

Hope this helps makes sense, and as far as I can see only the first parameter can be changed to achieve the same result.

 

[l_eonandr]

Doesn't work for me??
Can you explain the "1/B$2:B3=C3" What is the C3? is there a value that has to be entered in C3?
I get #N/A in the cell I put the formula in.
I tried the formula as is then changed it to fit my application, "=LOOKUP(F1,(1/A$20:A16000)=D1,B$20:B16000)", where I typed the value I wanted to search for in F1 and the column to search was A:A
and the value I wanted returned was in Column B:B. By the way the data starts in Row 20 and the last row is not known in some cases, this test case it was known.
Can you help me please to understand this formula. Thanks