?
Solved

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

Posted on 2013-01-03
3
Medium Priority
?
410 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 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