Hi,
i have searched a lot but in everywhere it finds 1st, 2nd, 3rd value and so on. I want to find 2nd last non blank value using index match or vlookup match function.
example
sheet2
A B
1 Name Due
2 John 350
3 Alan 250
4 Chris 100
5 John 650
6 Chris 75
7 Alan 550
8 John 125
Sheet1
A B
1 Name Due
2 John
3 Alan
4 Chris
I have attached sample workbook also.
I want that on Sheet1 column B should be calculated.
John 650
Alan 250
Chris 100
Sorry for my bad English.
Please Help. Thanks!!!
Last edited by gr8spot; 06-20-2015 at 06:39 AM.
Hi
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
☚ Click ★ just below left if it helps, Boo?ath?
Please supply sample workbook with results: is 350 applied to every name ? or if there is only one value for a name, is that applied i.e Alan=250?
I have attached sample workbook. please find the attachment.
B2=IF(COUNTIF(Sheet2!$A$2:$A$7,$A2)>1,INDEX(Sheet2!$B:$B,LARGE(INDEX((Sheet2!$A$2:$A$7=$A2)*ROW(Sheet2!$A$2:$A$7),0),2)),"")
Please Login or Register to view this content.
try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I want 2nd last value of column. lets say if add another entry in sheet 2 john 125 then B2 result should be 650.Originally Posted by nflsales
B2=IF(COUNTIF(Sheet2!$A$2:$A$7,$A2)>1,INDEX(Sheet2!$B:$B,LARGE(INDEX((Sheet2!$A$2:$A$7=$A2)*ROW(Sheet2!$A$2:$A$7),0),2)),"")Please Login or Register to view this content.
try this and copy towards down
with a helpcolumn and a pivot table.
see the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
thanks oeldere for reply.
But this doesnt solve the problem. the result i am expecting is something else. this is totally different.
Perhaps something like this? using array formula.........
Thank you so much azumi. you really saved my day!!
Glad it works......thanks for the feedback
=IF(COUNTIF(Sheet2!$A:$A,$A2)>1,INDEX(Sheet2!$B:$B,LARGE(INDEX((Sheet2!$A:$A=$A2)*ROW(Sheet2!$A:$A),0),2)),"")try thisPlease Login or Register to view this content.
Try this...
Sheet2
Sheet1
This array formula** entered in B2 and copied down:
=IFERROR(INDEX(Sheet2!B:B,LARGE(IF(Sheet2!A$2:A$8=A2,ROW(Sheet2!A$2:A$8)),2)),"")
** 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.
Edit: Removed this statement as it does not apply in this application.
Last edited by Tony Valko; 06-20-2015 at 03:33 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Both worked. thanks guys.
You're welcome. We appreciate the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks