Hi,
I am trying to get the below formula to choose the last instance of the value in the column, The formula I am currently using is shown below, Can anyone help me modify this? I think I need to use sumproduct in some way but cant work it out.
=INDEX('X08'!$L$2:$L$4935,MATCH('Pre-Call'!$H4118&'Pre-Call'!$A4118,INDEX('X08'!$G$2:$G$4935&'X08'!$A$2:$A$4935,0),0))
Answer withdrawn. It wasn't an answer that worked...
Last edited by Glenn Kennedy; 07-28-2015 at 04:55 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Match formula will end with the first instance itself also yours is an array formula
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Is there a way that I can get the last instance using the current formula or do I need a new formula?
You should use Glenn Kennedy suggestion to get the last instance. Because Lookup() will always arrive the last instance of the matching value.Originally Posted by Burt_100
I have a solution, but the exact formula depends on what you are matching and concatenating (numbers or text). Please attach a sample spreadsheet - just showing the sort of data you have.
If text: try this (entered as an array with CTRL-SHIFT-ENTER):
=INDEX('X08'!$L$2:$L$4935,MAX(('Pre-Call'!$H4118&'Pre-Call'!$A4118='X08'!$G$2:$G$4935&'X08'!$A$2:$A$4935)*MATCH(ROW('X08'!$G$2:$G$4935),ROW('X08'!$G$2:$G$4935))))
Here it is, working in a single sheet. You can adapt it, with a bit of luck!!
Last edited by Glenn Kennedy; 07-28-2015 at 05:56 AM.
Works great thanks.
You're welcome and thanks for the Reputation.
Or
In G2 Cell - Normal Formula
=IFERROR(LOOKUP(2,1/((A2:A14=E2)*(C2:C14=F2)),B2:B14),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks