Hi!!! I'm so happy. I found a formula that looks up the last value in a column of duplicate values. It goes like this: =LOOKUP(2,1/('04-13'!F3:F2848=A39),'04-13'!G3:G2848) Noooow, I would like to know if it is possible to modify this to get the SECOND to last value (and third, fourth too, if possible) Please help!!!
To help us help you, can you post workbook showing your actual data layout and a mockup of the results as you need to attain them?
Make sure there is just enough data to make it clear what is needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Sure, although every time someone asks for data, nobody else ever responds lol, but here it goes. This is the new formula =LOOKUP(2,1/(B1:B8=E3),C2:C8) and it finds the corresponding value to the final value of Bob in column B. NOW, I want to use the same formula but modify it to find the NEXT TO LAST value of Bob in column B. The spreadsheet I have has tons of values for Bob, so even though in this spreadsheet it is the first value, I need the formula to find the value of Bob right before the last value of Bob. Can you do it!!????
You can't use the same formula, this is a trick formula designed to display the last value that matches all the criteria.
You'll need a different creature to work through all the variations and pull them back, an Array formula.
If you really want the LAST, then the SECOND LAST, etc, you use the LARGE function in the formula below. If you change your mind and decide you want to list them all starting from first to last, then change that to SMALL.
For reference:
F2: Dept2
G3: Dept1
Array formula:
F3: =INDEX($C$1:$C$8,LARGE(IF($B$1:$B$8=$E3,ROW($B$1:$B$8),""),COLUMN(A1)))
...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.
Now copy cell F3 and paste on G3.
I don't understand the F2 Department 1 and G3 Department 2. What do you mean by that? We aren't even using those values.
This doesn't make any sense to me lol. I'm looking to at column B and trying to get the second to last value in that column and return the value to the right of it (in column C). How is your formula accomplishing this? It's also not working by the way...it just returns 0 but I'm probably inserting it incorrectly.
Last edited by amartino44; 06-11-2013 at 06:49 PM.
Those are merely instructions on waht to type into a cell to create a table with headers. Just do it. Put those values in those cells, then that array formula in that cell, then copy that cell to the right. You'll see.
In the array formula given, you can replace the COLUMN(A1) with just the number 1 to get the last value that matches. Change it to a 2 to get the second to last, and so on.
This still returns 0. Would you mind attaching the sheet with your suggested changes?
The array formula is what you're missing, and it won't help to see a sheet where I put it in, you have to "feel the difference" when entering an array formula for yourself.
1) doubleclick on F3 to open the blinking edit cursor for that cell
2) paste in the formula as given
3) now press CTRL-SHIFT-ENTER to confirm the formula. That's the weird step.
If you just press ENTER at the end, you get 0. If you press CTRL-SHIFT-ENTER, the array is activated (see the curly {} braces?) and the value 5 should appear.
Oops, I figured it out. I had deleted the lookup value. Now it works. Let me try this on my original sheet. Can you explain exactly what this is doing? Very helpful.....
Last edited by amartino44; 06-11-2013 at 07:14 PM.
Watch this: http://screencast.com/t/FBPddDuEkZ
My apologies if your up on arrays, most peeps coming here at first aren't.
Try this array formula**:
=INDEX(C:C,LARGE(IF(B2:B8=E3,ROW(B2:B8)),N))
Where N = the number of the instance you want to find from the bottom to the top.
If N=1 then the formula will find the bottom-most instance. If N=2 then the formula will find the 2nd instance from the bottom.
If you're wanting to find ALL instances then it's easier to look from the top down.
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@JBeaucaire...When I try to change the column value to just 1 (or 2), it gives me an error and won't compute.
Last edited by amartino44; 06-11-2013 at 07:20 PM.
Post your edited formula in the workbook so I can see the results. Or just use the COLUMN(A1) for 1, column(B1) for 2, etc.
COLUMNS($A1:A1) is more robust.Originally Posted by JBeaucaire
Using COLUMN(A1) leaves the formula more vulnerable to new column insertions which could lead to incorrect results.
Interesting, I had the exact same reservations regarding the COLUMNS($A$1:A$1) method. As it is ranged, if someone insert a column that range would expand throwing off the desired value.
Last edited by JBeaucaire; 06-11-2013 at 08:00 PM.
If you use COLUMNS($A1:A1) as the incrementer it will ALWAYS evaluate to 1 even if you insert a new column A. If you insert a new column A then it changes to COLUMNS($B1:B1) and still evaluates to 1.
If you use COLUMN(A1) as the incrementer it will "break" if you insert a new column A. It will change to COLUMN(B1) evaluating to 2.
To amartino44: If you are using the workbook that you posted, the calculations are set to manual so that is what may be giving you problems.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
A bigger issue is that the formula isn't working in my real worksheet. This is the modified formula. =INDEX('05-13'!G8:G2885,LARGE(IF('05-13'!F8:F2885=A9,ROW($F$8:$F$2885),""),COLUMN(A1)))
In a tab called "5-13", Column G has the values that I want to return. Column F has the lookup value. In the sheet with the formulas A9 has the lookup value. Why isn't this working? It returns a value that doesn't match up
Index the entire column:
=INDEX('05-13'!G:G,LARGE(IF('05-13'!F8:F2885=A9,ROW($F$8:$F$2885)),COLUMNS($A1:A1)))
Are you copying that formula across a row?
What cell are you entering that formula in?
I'm not saying that COLUMNS(...) is "bulletproof". It's not, but it's more robust than using COLUMN(...).
You can break almost any formula if you try hard enough!
And I seem to remember having this same discussion with you on the newsgroups a few years ago ... <bg>
Mind you, I do tend to use COLUMNS and ROWS now, so you must have influenced me on that.
Pete
Those were the good old days. Everyone had the same title, none!
I do what I can (although some people don't want to listen, oh well).Mind you, I do tend to use COLUMNS and ROWS now, so you must have influenced me on that.
Change to ranges in that formula to start in row1, not row8. Always start in row1 even if you don't need to, theyll be skipped and the MATCH function will target correctly. Try it.
It worked!!!!!! Yeeeeeeeeeeeeeeeees. Thank you both so muchOh, can you explain what this formula is doing? It's great to have the solution but I'd like to understand it too haha.
Wait. I sang too soon. It's only working for the last instance, not the second to last instance. When I change the $A1:A1 to $B1:B1 nothing changes.
Let me repeat these questions:
Are you copying that formula across a row?
What cell are you entering that formula in?
This is the formula that I am using =INDEX('05-13'!G:G,LARGE(IF('05-13'!$F$1:$F$2885=B9,ROW($J$1:$J$2885)),COLUMNS($B1:B1)))/1000
I am using it in BT9 and then I will drag it downwards in the column, the lookup value B9 will change accordingly. The report is in another sheet '5-13'. The formula with the $A1:A1, which starts in BT39 is working fine.
If you're drag copying the formula DOWN a column then you DON'T want to use neither COLUMN nor COLUMNS. You would use those functions to increment the N value as you drag copy the formula ACROSS a row. If you need to increment the N value as you drag copy DOWN a column then you would use the ROWS(...) function.
It sounds like you want the 2nd from last instance and this is constant and is not variable so there's no need to use an incrementer (COLUMN or COLUMNS).
So, just use a constant for the N value of the LARGE function.
Array entered**:
=INDEX('05-13'!G:G,LARGE(IF('05-13'!$F$1:$F$2885=B9,ROW('05-13'!$F$1:$F$2885)),2))/1000
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Beautiful. Could you please explain what this formula is doing? I'm not good with arrays
I'll give you a "deluxe" explanation later on this evening when I have more time (I'm the worlds slowest typer!
Great thanks. I'll be looking forward to it
I have one more question. If I want to change the formula to the 3rd, 4th from last can I just write 3,4 etc in the same formula? Also, if I want to change all references to worksheet 5-13 to 6-13, 7-13 etc, how could I do that. Couldn't I reference a cell, maybe an indirect, with the month and then include it in all the formulas? I guess I could just do a search and replace as well but adds a step. Thanks.
=INDEX('05-13'!G8:G2885,LARGE(IF('05-13'!F8:F2885=A9,ROW($F$8:$F$2885),""),COLUMN(A1)))
The COLUMN() part of the formula is supposed to increment FOR YOU as you copy that cell (not the formula, the cell with the formula in it) to the right.
If you change that to ROW(), then it will increment for you as you copy the cell downward.
You can skip that whole thing and replace the COLUMN() or ROW() reference with the number you want. 1, or 2, etc.
===========
Yes, you can add an INDIRECT() method into that already horrible to read formula. I don't recommend it, but you can. I'd suggest copying the formulas to the new range, highlighting them, then using CTRL-H to open the REPLACE wizard.
You can do all of those things but we need to know what your end goal is.
We're offering suggestions to your questions but before you get those working correctly you're already wanting to expand the scope of what you're doing.
At this point I don't know what to suggest because we haven't resolved the original problem/question/request yet.
Can you post a SMALL sample file (no bigger than 50kb) that shows your data and the results you expect?
I guess horrible is relative!
Yes, it does take a while to get used to reading mega-formulas. I lose my mind trying to explain these, it's just so many concepts working in tandem for what is a seemingly simple result. That's why I'm tempted to write UDFs for some of these just so the formula being used reads more logically to the eye.
Yeah, sometimes it's hard to explain how things work in such a way that the OP actually understands it.
I used to love to do this but not so much any more!
In addition...
That's also a good way to tell the contenders from the pretenders.
Ask them to explain (in detail) how something they suggested works. If they know what they're doing the explanation will be clear and concise. If it's something they just copied/pasted then the explanation will just be mumbo jumbo that that leaves the OP confused.
What do you guys mean? That formula did solve my problem. I was just asking you to explain what the formula was doing exactly and then seeing if I could use an indirect and use 2,3,4,5 if my list is really big.
Several things happening from the inside out in this construct. The basic formulas you need to know are:
SMALL(array, k)
IF(test, trueresult, falseresult)
INDEX(range, row/col_num)
The SMALL() is inside the IF(). It's purpose is to create an array of row numbers within the SMALL(array, k) of only the row numbers that are TRUE in the IF test.
Once that array is created, the number in the "k" is where in that array we want to take a number from.
Once that number is extracted, it is fed up into the INDEX() formula as the second parameter, the row/col_num, the value from [B]range[/B in that position is returned.
The "k" trick is something we've been discussing. You can hardcode a number there for what you want in that cell, a 1 or a 2, whatever. But if you want to be able to copy your cell across columns and have the "k" value increment itself, that's why we use COLUMNS($A$1:$A1) or I used COLUMN(A1).
Were you able to follow this explanation? I notice you have a tendency to simply stop responding to your threads. You need to be the last to answer, to indicate you understand and are using what has been offered.
Then, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.
Small? We aren't using a small in the formula lol.
SMALL and LARGE are the same creature, apologies if that throws you. They work the same way, one works from smallest>UP, the other works from largest>DOWN.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks