lookup 2 values and return one

[jbutzer72]

I need to lookup 2 values in a spreadsheet and return one value. I attached an example -


From the MAIN tab, the stunum is the lookup value...I need to go to the DATA tab and look for the stunum AND the test # and return the corresponding test grade.

This is a tiny example...my real data contains thousands of rows of data.

Please help!

[TMS]

One way:

=INDEX(Data!$D$2:$D$7,MATCH($B2&SUBSTITUTE(C$1," Score",""),Data!$B$2:$B$7&Data!$C$2:$C$7,0))

Array Entered with Ctrl-Shift-Enter rather than just Enter


Regards, TMS

[jbutzer72]

Awesome...that did work for Test # 1 but I'm not as advanced as you...can you show me the same formula for Test 2 and I will see if I can figure out how you are building this formula?
I'm not quite following all of it yet....thank you so much!!!!

Also what is the difference between just "enter" and CTRL-SHIFT-ENTER?

[TMS]

Thanks for the rep.

To get Test #2, just drag the formula across. It's just removing " Score" from the column header.

it's an Array Formula and needs to be committed with Ctrl-Shift-Enter rather than just Enter. However, you only need to put the first formula in, commit it, and then drag across and down.

Regards, TMS

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.