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

	A	B	C	D	E	F	G	H	I
1	Name	Activity Completed on Time	First Intro Completed on Time		Row Labels	Activity	First Intro	Grand Total
2	Steve	92.25%	44.67%		David	0.10%	0.43%	0.16%
3	David	0.93%	10.60%		Completed On Time	0.93%	10.60%	5.80%
4	John	66.67%	0.00%		Completed Overdue	99.07%	89.40%	94.20%
5					John	0.01%	0.02%	0.01%
6					Completed On Time	66.67%	0.00%	46.67%
7					Completed Overdue	33.33%	0.00%	23.33%
8					Due Late	0.00%	100.00%	30.00%
9					Steve	0.18%	0.66%	0.28%
10					Completed On Time	92.25%	44.67%	70.46%
11					Completed Overdue	7.75%	54.73%	29.27%
12					Due Late	0.00%	0.59%	0.27%

<tbody>
</tbody>

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