Matching instead of VLookup

[kiyo052]

I am trying to figure out the formula for this and I can't seem to get it done:

Everything in Column B must equal the "Completed on Time" in column F
Everything in Column C must equal the "Completed on Time" in Column G


For example, I need:
B2= F10
C2= G10
B3= F3
C3= G3
B4= F6
C4= G6


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Activity Completed on Time[/TD]
[TD="align: center"]First Intro Completed on Time[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Row Labels[/TD]
[TD="align: center"]Activity[/TD]
[TD="align: center"]First Intro[/TD]
[TD="align: center"]Grand Total[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]92.25%[/TD]
[TD="align: center"]44.67%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]David
[/TD]
[TD="align: center"]0.10%[/TD]
[TD="align: center"]0.43%[/TD]
[TD="align: center"]0.16%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]David
[/TD]
[TD="align: center"]0.93%[/TD]
[TD="align: center"]10.60%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed On Time[/TD]
[TD="align: center"]0.93%[/TD]
[TD="align: center"]10.60%[/TD]
[TD="align: center"]5.80%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]66.67%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed Overdue[/TD]
[TD="align: center"]99.07%[/TD]
[TD="align: center"]89.40%[/TD]
[TD="align: center"]94.20%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]0.01%[/TD]
[TD="align: center"]0.02%[/TD]
[TD="align: center"]0.01%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed On Time[/TD]
[TD="align: center"]66.67%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]46.67%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed Overdue[/TD]
[TD="align: center"]33.33%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]23.33%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Due Late[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]100.00%[/TD]
[TD="align: center"]30.00%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Steve[/TD]
[TD="align: center"]0.18%[/TD]
[TD="align: center"]0.66%[/TD]
[TD="align: center"]0.28%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed On Time[/TD]
[TD="align: center"]92.25%[/TD]
[TD="align: center"]44.67%[/TD]
[TD="align: center"]70.46%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Completed Overdue[/TD]
[TD="align: center"]7.75%[/TD]
[TD="align: center"]54.73%[/TD]
[TD="align: center"]29.27%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Due Late[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.59%[/TD]
[TD="align: center"]0.27%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for anyone that can figure this out!!

 

[Johndinho]

Hi,

Paste this into B2 and copy down the table
=OFFSET(INDIRECT(CONCAT("E",MATCH(A2,$E:$E,0))),1,1)

Paste this into C2 and copy down the table
=OFFSET(INDIRECT(CONCAT("E",MATCH(A2,$E:$E,0))),1,2)


For Steve it works as..
The MATCH element returns the row of the name 2
CONCAT creates the cell reference as text E2
INDIRECT activates the text as a cell reference
Offset moves from that cell reference 1 row down and 1 column across
(Column C is 1,2 to move 1 row down and 2 columns across)

Hope that helps..

 

[kiyo052]

Thank you for the help! Is there another way to do this without using Concat? The information from E to H is going to be on a different sheet. Someone wrote a different formula but it doesnt seem to be working:

=IFERROR(INDEX($F:$G,MATCH($A:$A,$E:$E,0)+1,MATCH(D$1,$F$1:$G$1,0)),0)

Any suggestions?

 

[Johndinho]

Let me think overnight..

What are the sheet names?

 

[Johndinho]

The IFERROR function should be working but wouldn't if the data is on different sheets.

Let me know the layout of the sheets and I'll take a look