1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Lookup last value using 2 criteria

Discussion in 'Ask an Excel Question' started by Busymanjohn, Apr 20, 2017 at 8:01 AM.

1. ### BusymanjohnMember

Messages:
162
Hi Guys, I am having problems with a formula, I am trying to lookup a range of cells and return the last value in that range based on 2 criteria ,,, I can return the last value based on 1 criteria using LOOKUP(2,1/(B3:B16=F6),C3:C16), but how do I insert the 2nd criteria ,,,, also looked at Index & Match, doesn't quite get me the result ( although I could be missing something ). Sample file attached ..... the results I want to appear in cells G6 and G7 ,,, range is in A3:C16, criteria is F2 and F6 ( time range and date ). Any ideas?

File size:
9 KB
Views:
8
2. ### HuiExcel NinjaStaff Member

Messages:
10,277
G6: =INDEX(\$C\$4:\$C\$16,SUMPRODUCT(--MAX((\$A\$4:\$A\$16=\$F\$2)*(\$B\$4:\$B\$16=F6)*(ROW(\$A\$4:\$A\$16)-3))))
copy down
Thomas Kuriakose likes this.
3. ### bosco_yipWell-Known Member

Messages:
875
In G6, copy down :

=LOOKUP(2,1/((A\$4:A\$16=F\$2)*(B\$4:B\$16=F6)),C\$4:C\$16)

Regards
Thomas Kuriakose likes this.
4. ### BusymanjohnMember

Messages:
162
Thanks guys, both solutions worked a treat .....