Hi all,
I am trying to extract data from a column based on the information of two columns. In this picture http://i.imgur.com/2IbOKjO.jpg, the left and middle panels represent my current data.
I would like to extract "Number" from the middle panel, based on the "Name" and "Year" of my left panel, to create a table like the one on the right panel of the picture.
I have attached the excel file for your convenience.
Thank you.
Alex
Hi Alex,
So you already have Name and Year in a column, and want to find the corresponding Number from column G? If so, enter this in C2 and fill down:
Attached is the Excel file if it helps.
Please Login or Register to view this content.
Try this...
Entered in C2 and copied down as needed:
=INDEX(G$2:G$12,MATCH(A2,E$2:E$12,0)+COUNTIF(A$2:A2,A2)-1)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
SteelReyn: Thank you so much that works very well!
Tony Valko: The formula works fine for the attached data thank you! But for some reason, when I extended the range in the formula, the "Number" doesn't match correctly.
C2: =sumifs(g:g,e:e,a2,f:f,b2)
The formula is based on the fact that the data in the sample file is sorted. If your real data is not sorted then the formula will not work correctly.Originally Posted by alexpsyched
If the combination of name plus year is unique then I think TM's suggestion is the way to go.
Thanks for clarifying. I should've been more clear in my question.
You're welcome!
Good luck!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks