Help with a Formula Please

[Corca]

Hi,

Need help please with:

I have a data sheet with many columns of data. I have another sheet with a
summary of some of the data as follow:

Result sheet has a name column and a high score column. I used the large
function to find the 84. Now i need a formula to find the 84 on the data
sheet and the go accross the same row to get name and place it on the result
sheet under name beside the 84.


The data sheet has about 60 columns, the 84 is found on 6 columns all next
to each other no in a single column.

Can someone help please.

Name High score
??????? 84

 

[Jacob Skaria]

You could try a combination of INDEX() and MATCH(). Let us know how your data
is arranged...

 

[Corca]

Hi,

Result sheet is:

A B C D
1 Name 84
2
3


Data Sheet:

a b c d e f g h i
1 A Tom W L 30 45 84 30 15
2 A Sam
3 B Tim

Say A = Grade, B=Name, C=win, L=loss, e to i have high breaks

I used trhe Large function to find the 84, now need a formula in name feild
in result sheet to find corresponding name for 84 in data sheet. I tried
vlookup but no good.

appreciate the help.

 

[Jacob Skaria]

Suppose you have the data sheet in Sheet1

Summary sheet B1 = 84
In cell A1 apply the below formula. Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=INDEX(Sheet1!B:B,MIN(IF(Sheet1!E1:I3=B1,ROW(Sheet1!E1:I3))))

 

[Corca]

Thanks, worked, now I try it on my actual sheet.

Suppose you have the data sheet in Sheet1

Summary sheet B1 = 84
In cell A1 apply the below formula. Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=INDEX(Sheet1!B:B,MIN(IF(Sheet1!E1:I3=B1,ROW(Sheet1!E1:I3))))