Index Match Two columns Find Last Bottom

Thanks for all your help in the past, I have a list of data containing lets
say two columns. I need to find the bottom or last which meets the criteria
of column A. For instance,
Column A Column B
134 Jack
168 Frank
170 Sally
134 Rod
170 Amy
What I need is where A = 134, I want to find the last used cell where A =134
in which the answer is "Rod". I have searched for a while with other type
solutions but none for exactly what I need. Thanks in advance. My data
contains about 100 rows sometimes the data is the same 134 = Rod, but all I
want is the last cell entry where A =134.

Assuming that A2:B6 contains the data, try...

=LOOKUP(2,1/($A$2:$A$6=134),$B$2:$B$6)

or

=LOOKUP(2,1/($A$2:$A$6=D2),$B$2:$B$6)

....where D2 contains 134.

Hope this helps!

In article <[email protected]>,
henryriver1 <[email protected]> wrote:

> Thanks for all your help in the past, I have a list of data containing lets
> say two columns. I need to find the bottom or last which meets the criteria
> of column A. For instance,
> Column A Column B
> 134 Jack
> 168 Frank
> 170 Sally
> 134 Rod
> 170 Amy
> What I need is where A = 134, I want to find the last used cell where A =134
> in which the answer is "Rod". I have searched for a while with other type
> solutions but none for exactly what I need. Thanks in advance. My data
> contains about 100 rows sometimes the data is the same 134 = Rod, but all I
> want is the last cell entry where A =134.

Thanks, works like a dream. I am using this for a Real Time Reporting for
our production and they want to know what product is being ran at the current
time.

"Domenic" wrote:

> Assuming that A2:B6 contains the data, try...
>
> =LOOKUP(2,1/($A$2:$A$6=134),$B$2:$B$6)
>
> or
>
> =LOOKUP(2,1/($A$2:$A$6=D2),$B$2:$B$6)
>
> ....where D2 contains 134.
>
> Hope this helps!
>
> In article <[email protected]>,
> henryriver1 <[email protected]> wrote:
>
> > Thanks for all your help in the past, I have a list of data containing lets
> > say two columns. I need to find the bottom or last which meets the criteria
> > of column A. For instance,
> > Column A Column B
> > 134 Jack
> > 168 Frank
> > 170 Sally
> > 134 Rod
> > 170 Amy
> > What I need is where A = 134, I want to find the last used cell where A =134
> > in which the answer is "Rod". I have searched for a while with other type
> > solutions but none for exactly what I need. Thanks in advance. My data
> > contains about 100 rows sometimes the data is the same 134 = Rod, but all I
> > want is the last cell entry where A =134.
>

=INDEX(A1:A20,IF(B1:B20="Rod",A1:A20),ROW(A1:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"henryriver1" <[email protected]> wrote in message
news:[email protected]...
> Thanks for all your help in the past, I have a list of data containing
lets
> say two columns. I need to find the bottom or last which meets the
criteria
> of column A. For instance,
> Column A Column B
> 134 Jack
> 168 Frank
> 170 Sally
> 134 Rod
> 170 Amy
> What I need is where A = 134, I want to find the last used cell where A
=134
> in which the answer is "Rod". I have searched for a while with other type
> solutions but none for exactly what I need. Thanks in advance. My data
> contains about 100 rows sometimes the data is the same 134 = Rod, but all
I
> want is the last cell entry where A =134.

Don't know what happened there, should have been

=INDEX(B1:B20,MAX(IF(A1:A20=D2,ROW(A1:A20))))

still array formula.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =INDEX(A1:A20,IF(B1:B20="Rod",A1:A20),ROW(A1:A20))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with googlemail if mailing direct)
>
> "henryriver1" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for all your help in the past, I have a list of data containing
> lets
> > say two columns. I need to find the bottom or last which meets the
> criteria
> > of column A. For instance,
> > Column A Column B
> > 134 Jack
> > 168 Frank
> > 170 Sally
> > 134 Rod
> > 170 Amy
> > What I need is where A = 134, I want to find the last used cell where A
> =134
> > in which the answer is "Rod". I have searched for a while with other
type
> > solutions but none for exactly what I need. Thanks in advance. My data
> > contains about 100 rows sometimes the data is the same 134 = Rod, but
all
> I
> > want is the last cell entry where A =134.
>
>