Hi all,
I am trying to build a database using index/match that will return the most recent date an item was worn. In the attached spreadsheet I've entered formulas in cells F5 and I5, and test values below in columns F and I. Both formulas are returning dates on which the lookup value was not even entered.
Any help you can provide would be much appreciated!
Tracker.xlsx
MATCH(F$4,F$12:F$2000,1) against
Maison Scotch Tweed
JC Navy Schoolboy
Loft Plum Peplum
Black Ponte Peplum
will not work as data needs to be in ascending order
Black Ponte Peplum
JC Navy Schoolboy
Loft Plum Peplum
Maison Scotch Tweed
try
=INDEX(A12:A60,LOOKUP(2,1/--(F12:F60=F4),ROW(A1:A1000)))
Last edited by martindwilson; 02-03-2013 at 03:48 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi,
Change the last parameter in your MATCH function to False (or 0 if you prefer).
i.e.
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
richard that wouldnt work if he wore the same item twice which i assume is possible..but you never know
Hi Martin,Originally Posted by martindwilson
Indeed not. Thanks for the correction. I missed the bit about the most recent date.
Hi Richard,
Thank you for the advice. I have already tried all variations of match type: 0,-1, 1, false, true. Unfortunately using false or 0 returns the first date, not the most recent. I do need to keep the layout of the spreadsheet in ascending order by date, so re-sorting is not an option.
Tracker.xlsx
Hi Martin,
Perhaps I am misunderstanding your advice, but when using your suggested formula the date returned is about 8 years ago. See attached. Thanks again for your help.
Last edited by cartman88; 02-03-2013 at 03:59 PM. Reason: updated attachment
=IF(ISBLANK(F$4),"",INDEX(A12:A60,LOOKUP(2,1/--(F12:F60=F4),ROW(A1:A1000))))
the row bit creates a range 1,2,3,4,..1000
you dont need a1:a1000 you could put z1:z100
=IF(ISBLANK(F$4),"",INDEX(A12:A60,LOOKUP(2,1/--(F12:F60=F4),ROW(Z1:Z1000))))
Last edited by martindwilson; 02-03-2013 at 04:06 PM.
Worked like a charm, thank you. Would you mind explaining the logic behind the vector? I've never seen that done before.
Try this user defined function.
Open the VBA editor by hitting Alt F11.
Please Login or Register to view this content.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.
In a suitable F4, enter =GetLatest(F4,F$12:F$2000)
Remember to save the workbook as a macro enabled workbook .xlsm
Martin
LOOKUP(2,1/--(F12:F60=F4),ROW(A1:A1000))))
FIRST BIT
1/--(F12:F60=F4)
(F12:F60=F4)
produces a range of
true /false
{false;false;true;true........}
use -- to change to 1 and 0
{0;0;1;1...}
now take 1 and divide every thing in the range by it
you get
{#DIV/0!;#DIV/0!;1;1;#DIV/0!....#DIV/0!}
look up 2 against that range it ignores #DIV/0!
and finds the last value less than 2
in this case it would be the 4th value
#DIV/0!;#DIV/0!;1,1
now row(a1:a1000) creates a range of (put it in a cell on its own =row(a1:a1000) and evaluate the formula you can see it)
{1;2;3;4,..1000}
so the lookup finds the last 1 in the lookup vector in position 4
and returns the value in the 4th position of the results vector which is 4(when you tried it you used row(a12:a100) which would have given {12;13;14;15;16;....100} so the same formula would have returned 15 from the 4th position)
now use that value of 4 in the index
index(a12:a1000,4)
Last edited by martindwilson; 02-03-2013 at 05:09 PM.
shorter formula in F5 (drag-left or right after that), since coercing and INDEX not required when LOOKUP is being used in this fashion:
Please Login or Register to view this content.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
yep i dont know why i was using index or row() i think it was coz i was working on something else at the same time!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks