?
Solved

Find all values (names) in a column and list in a different range - Formula Dashboard

Posted on 2013-01-03
3
Medium Priority
?
402 Views
Last Modified: 2013-01-03
Not sure if the title best describes what I am trying to do. I have range (A1:C13) that contains a column of names, test dates and scores. J1 contains the name that I want to search through the table and list all of their test dates (dynamic list in F:G column). From the list of test dates I have entered an Array formula that will look up their score. I also added a chart that will change with each date entered. (It will be a mini dashboard that updates with new scores).

I am a little lost how how to go through the column find each entry for the name listed in the J column. I could do a vlookup, but it would stop at the first one it finds. I think it would be using the index function, small and an if statement based on a google search, but it is a bit over my head.

I attached a workbook to explain a little better. Thank you in advance for any help.
Brent
EE---Match-Index-Question--1-.xlsm
0
Comment
Question by:bvanscoy678
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 38740829
Hello Brent, assuming nobody will take two tests on the same day you could use this formula in F3

=IFERROR(INDEX(B$2:B$13,SMALL(IF(A$2:A$13=J$1,ROW(A$2:A$13)-ROW(A$2)+1),ROWS(F$3:F3))),"")

confirmed with CTRL+SHIFT+ENTER and copied down as far as you might need

...and this version in G3 (your formula but with added IF function)

=IF(F3="","",INDEX(C$2:C$13,MATCH(1,(J$1=A$2:A$13)*(F3=B$2:B$13),0)))

also "array entered"

see attached

Edit: if it's possible for people to have two tests on the same day then the F3 formula can remain the same but G3 version should be this

=IFERROR(INDEX(C$2:C$13,SMALL(IF(A$2:A$13=J$1,IF(B$2:B$13=F3,ROW(A$2:A$13)-ROW(A$2)+1)),COUNTIF(F$3:F3,F3))),"")

regards, barry
multi-lookup.xlsm
0
 

Author Comment

by:bvanscoy678
ID: 38740956
Barry,

Yes, that works. No wonder it was above my head. To answer your question, it will not be possible to take more than one test in a day, so the first formula will work.

Thank you for the time and answer,
Brent
0
 

Author Closing Comment

by:bvanscoy678
ID: 38740958
Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question