Hi all
I did a search on this problem but none seemed to apply so far. The question is to retrieve the last 3 values of a search criteria from a set of data, kind of a Vlookup function with a twist.
I included a small sample file which will serve to define the question much better. Thanks in advance for your help.
hi ant1905, try this array formula:
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Last edited by benishiryo; 03-14-2013 at 03:31 AM. Reason: added file
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hello Benishiryo
Thank you for your help. I got a NAME error... Still checking where it went wrong since I had to translate the formula..
Benishiryo just as I suspected I have done a translation mistake now its all good ... thank you so much
what if there is only one value next to e.g. aaa then it returns an error... seems like I gotta have at least 3 values next to the search criteria to get it working
is that correct?
using Excel 2007 & above, you can use IFERROR to counter errors:
=IFERROR(MyFormula,"")
Excellent! Much appreciated Benishiryo. Sincere thanks .
Hi all,
I tried to modify the above formula for a more simple mission but I think I got mixed up somewhere along the way ....
I am trying to retrieve the last 3 numericly occupied cells in a row. Any help is most appreciated. Thanks in advance
Sample file attached
Try this
In T4
=INDEX($C4:$Q4;MATCH(9,99999999999999E+307;$C4:$Q4)-COLUMN(B4))
In U4
=INDEX(C4:Q4;MATCH(9,99999999999999E+307;C4:Q4)-COLUMN(A4))
In V4
=INDEX(C4:Q4;MATCH(9,99999999999999E+307;C4:Q4))
Copy down as you need.
** Change ALL semi colons in my formula to comma, if you have to do it. Also the comma after first 9 to .
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thanks a million Fotis ...
try this too:
=IFERROR(INDEX($C4:$Q4,LARGE(IF($C4:$Q4<>"",COLUMN($C4:$Q4)-COLUMN($C4)+1),4-COLUMNS($T4:T4))),"")
also an array formula
thank you benishiryo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks