Formula Help

[Guest]

Hi.

Spreadsheet has 2 tabs:

Tab 1 Tab 2
Age Name
Weight Age
Name Weight
Height

In Tab 3 in cell A1, I want to put a formula that says:

-If name on tab 1 matches name on tab 2; AND age on tab 1 matches age on
tab2, AND weight on tab 1 matches weight on tab2, THEN give methe
corresponding height.

Any help is always appreciated. Thanks.

 

[T. Valko]

On your Tab 1, are there "groups" of data like this for each person?

Tab 1
Age
Weight
Name
Height

If so, does each group of data follow the same pattern?

Biff

 

[Guest]

Not sure what you mean by "groups" but I can tell you that the data on each
tab does not follow any pattern. Tab 1 has 130 rows worth of info, Tab 2 has
400.

 

[T. Valko]

I think you're going to have to provide a more detailed description and
explanation.

Biff

 

[Guest]

Here goes:

Tab 1 Columns:

Name Age Weight Height

Frank 47 220 6'2"
Alice 18 120 5'4"
Bill 12 100 5'0"
Frank 30 200 5'9"

Tab 2 Columns:

Name Age Weight

Frank 47 220
Ed 40 250
Bill 12 100
Frank 30 200

You'll notice that there is no height column in tab 2. I need it to be on
tab 2 as well. You'll also notice that the name Frank appears twice. That's
why I need a formula to bring the height in to tab 2 that says, if name, age,
and weight all match, give me that person's height. I hope this helps.

 

[T. Valko]

Ok, that makes it pretty clear!

Assume you have this on Sheet1 in the range A36 -

Frank 47 220 6'2"
Alice 18 120 5'4"
Bill 12 100 5'0"
Frank 30 200 5'9"

Assume you have this on Sheet2 in the range A3:C6 -

Frank 47 220
Ed 40 250
Bill 12 100
Frank 30 200

Enter this array** formula on Sheet2 in cell D3:

=INDEX(Sheet1!D$3$6,MATCH(1,(Sheet1!A$3:A$6=A3)*(Sheet1!B$3:B$6=B3)*(Sheet1!C$3:C$6=C3),0))

Copy down as needed.

** array formulas need to entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff