Index Match with Multiple IF/THEN criteria

Okay, I'm hung up....under the NAME (Column F15) I want it to match A15 with A2:A9, and match E15 with B1:V1 and return the persons name that completed the task as shown in columns B, E, H, etc. Would be good to tie in which are COMPLETE and PENDING as shown in Columns C, F, I, etc. I have an example attached for reference.

Any help is GREATLY appreciated!

Try

Please Login or Register  to view this content.

in cell F15 and copy down as needed.

Try

=INDEX($B$2:$T$9,MATCH($A16,$A$2:$A$9,0),MATCH($E16,$B$1:$T$1,0))

Note that rows 18 and 19 will return errors because the numbers in E18 and E18 are formatted as text, you need to remove the leading apostrophes.

To get the status in the next column, use

=INDEX($C$2:$U$9,MATCH($A16,$A$2:$A$9,0),MATCH($E16,$B$1:$T$1,0))

And the dates in the last column

=INDEX($D$2:$V$9,MATCH($A16,$A$2:$A$9,0),MATCH($E16,$B$1:$T$1,0))

Hi jjhayes, it worked on a few cells, but was still returning an error where there was data (i.e. if you copy it down, cells F18 and F19 say #N/A)??

LOL Jason...I was typing that question when you must've posted. Thank you!

Thats because the 2 in cell E18 and E19 is formatted as text...change it to a number and it will work.

Okay...so the way my report will work is those cells in A15:E30 will just be pasted into the sheet...how do I keep them from formatting incorrectly? Whether I try to change to number format, or simply paste values from the other sheet it won't work....unless I retype the plant number shown in Column E15:E30...then poof it works perfectly

Okay, slight tweak...

Please Login or Register  to view this content.

Notice that we are taking the result in column E and multiplying by 1, this will tell the match to convert any text to number...does that make sense?

you guys are awesome! I freaking love this website! Thank you SO much for your quick help!!!!