formula to identify specific cells

[dmack]

Looking for a formula that will be in another worksheet that will identify
Jim had 5 oranges or art had 2 bananas. Would like to have it so that it
would identify Jim and any one of the headers in a range of cells

A2 A3 A4 A5
"Apples" "Oranges" "Pears" "Bananas"
B2 "Jim" 41 0 6 3
B3 "Don" 0 9 0 12

B4 "Art 5 5 0 2
B5 "Fran" 4 0 11 25

 

[Gary''s Student]

In a sheet called "paste area" in A1 thru E5:

Apples Oranges Pears Bananas
Jim 41 55 6 3
Don 0 9 0 12
Art 5 5 0 2
Fran 4 0 11 25


In another sheet in A1 thru A2:

Jim
Oranges

In B1 thru B2:

=MATCH(A1,'paste area'!A:A,0)-1
=MATCH(A2,'paste area'!1:1,0)-1

in C1:

=INDEX('paste area'!$B$2:$E$5,B1,B2)


to see:


Jim 1 55
Oranges 2


The formulas can be combined, but then they would be long.