Match Function Help

[ajayxlnc2]

Hi,

I have a list consisting of two columns. Team and the Count of that Team (A & B Colms). In that there are Total No. Of Sample1 Items, .... Total No. Of Sample8 Items (A2:A9) and there are Actuals Sample1,.......Actuals Sample8 (A10:A17) and in the subsequent columns i have the count for all these teams. This will be generated by system automatically. I have a simple Snapshot G4:H17 in other workbook.

I want to calculate Achievement % in G4:H17, actually this simple snapshot is in another workbook and it has to pull data from the system generated file A10:A17.

First it has to find the "Sample1" in the Team data and then calculate the % Achieved (Actuals/Total) and put the same in H4:H11 (which is not in the same sheet but in different Sheet.

Need your help in finding out a way to solve this.

 

[Sajan]

Hello ajayxInc2,
I would suggest changing the layout for your worksheet to make it easy to calculate stuff.

Col A=Team
ColB=Counts
ColC=Actuals

or you can setup two separate ranges:

colA=Team
ColB=Counts

ColC=Team
ColD=Actuals

Then you would be able to do simple lookups between the data sets.

If you need help with that, let us know.

Cheers,
Sajan.

 

[ajayxlnc2]

yeah, I need help in that......

 

[ajayxlnc2]

Hi sajan

Actually The snapshot is in a separate file i want the % to be calculated in other workbook, is that possible

 

[Xiq]

Hi there,
How about the following formula?

=INDEX($B:$B,MATCH(REPLACE($G:$G,1,11,"Actuals "),$A:$A,))/INDEX($B:$B,MATCH(REPLACE($G:$G,1,11,"Total No. Of ")&" Items",$A:$A,))

Replace the $A:$A references to the column in the correct workbook.
Do the same with $B:$B references.

 

[ajayxlnc2]

No This formula is not doing the trick which i wanted, can you please verify it.

 

[3G]

Here's what Sajan is talking about (I believe). I've created the named ranges as well (Team, Actual, Achieved, Total). You can simply do a lookup into these columns based on the ranges.

One thing I've learned is that data layout is tantamount to getting the right results, and being able to apply the formulas you want.

 

[Xiq]

Hi Aljayxlnc2,

See attached file. It's the same formula as I posted before.

 

[Sajan]

Hi AjayxInc2,
While Xiq's formula helps your current situation, I would still encourage you to restructure your data since it might allow you to use basic lookup functions.

Cheers,
Sajan.

 

[Xiq]

Agree