index? lookup? match? Which to use?

[erickguz]

Hello,

I have tried various options unsuccessfully. Your help is appreciated.

In Sheet 1, I have the following and need to fill in the "Scores" Column that is on Sheet 2.

SHEET 1:

COL A: DATES
COL B: NAMES
COL C: SCORES

8/1 John ____
8/1 Fred ____
8/2 Bret ____
8/2 Kyle ____
8/2 Ari ____
8/3
8/4...


SHEET 2:


COL A: DATES
ROW B1:AC1 = different names
B2:AC10000 = random scores

DATES John Fred Bret Kyle Ari
8/1
8/1 0
8/1 1
8/1 2
8/2 4
8/2 7
8/2
8/3


Please note that the DATES Column can have the same date repeat itself numerous times on both sheets.

Also, the above formula is what I seek, but would there be a different formula if the name were to repeat itself several times in one day?

Thanks again.
-E

[Tnesper]

I have to stipulate that I am not the best with excel, but have a little knowledge with index and match. I can't get this formula to work quite right, but it works for some of them. If you know enough about index and match, hopefully you can modify this to get it to work for you. Otherwise, someone with more experience well have to help. But hey, at least you get a free bump

=INDEX(Sheet2!$A$2:$E$6, ROW(),MATCH(Sheet1!B2,Sheet2!$B$1:$AC$1,0))  

[erickguz]

I noticed a formula that NBVC had for a prior thread. I adjusted and it worked well.

= INDEX( RESULTS! $N$9:$N$5000, MATCH(1,( RESULTS! $D$9:$D$5000=B10 ) * ( RESULTS! $L$9:$L$5000=D10 ) ,0 ))

However, the question remains... How do I adjust/change the formula if the NAMES repeat on the same DATE?

The above formula returns the same SCORE for all the times that a name has multiple entries on one date.

Thank you.

CONTROL+ALT+SHIFT...

[NBVC]

Can you perhaps attach a sample workbook, showing what you expect?

[erickguz]

Thank you for reply.

Attached is a Baseball Example of what I need.

-E

[NBVC]

Try this formula in E10:

Please Login or Register  to view this content.

which is also confirmed with CTRL+SHIFT+ENTER and then copy it to the next column and down both columns...

Note: I included an error trap to return a blank instead of an error message.

[erickguz]

This formula is longer than what I normally type in one day... But it works.

There is obviously some logic to the thought process involved in similar formula creation.

Thank you again.

(Interesting how the original formula I had in the worksheet - a hybrid of your creation in another thread, was very simple, but adding a small change elongated the formula 4x.) What did I just say? I'm not sure, but if you can write that formula, surely you can understand ...

Thanks.
-E

[NBVC]

When you start introducing multiple matches instead of just one, it can get a tad more complicated... especially when you are trying to match 3 fields not just 1.

Also, in this formula I added an error trap (IF(ISNUMBER(MATCH(1,(SCHEDULE!$D$9:$D$1000=$B10)*(SCHEDULE!$E$9:$E$1000=$C10)*(SCHEDULE!$F$9:$F$1000=$D10),0)

which is long again because it has to match 3 different fields...

The formula you got from my other post was for one record match and had no error trap.