If, and and vlookup statements in one formula

[Brent R Janetzki]

Hi Folks,I need a formula for the following.

Cell B127: Persons Gender (Male Female)
Cell B128: Age (Persons age
Cell: I192: Returns the average grip strength of the person

I am doing a grip strength assessment where by cell I192 returns the average grip strength over three attempts of the participant. I then want my formula to tell me where they sit in comparasion to the average for their age and gender which is recorded in another table that I will access via a vlooup table within the formula.

I need to write a formula that states if cell B127 is "Female" then take the age recorded in Cell B128, then go to the result in I192 and then go to my Vlookup table to return the value for the persons age, gender etc. I know how to do the vlookup part but just can't get the first part.

Thanks in advance

 

[bellman101]

Hi Brent,

You outline the scenario if B127 is female, but what if it is male?

and how does that affect the VLOOKUP (if at all)?

Cheers
JB

 

[Brent R Janetzki]

Hi Brent,

You outline the scenario if B127 is female, but what if it is male?

and how does that affect the VLOOKUP (if at all)?

Cheers
JB

Hi JB, thanks for your response. Sorry, I should have provided more info.

So if it's Female and aged 25 for exampled. I want the it to look at the cell were the average grip strength is recorded and then go to the female look up table in the other tap that I have set up. If it's male and aged 25 I then want it to go to the same cell where the average grip strength is recorded and then reference the male part of the vlookup table.

This is the reference of the applicable vlookup tables for Male/Female:

Male: =VLOOKUP(I194,'Grip Strength'!E2:G77,3)
Female: =VLOOKUP(I194,'Grip Strength'!A2:C77,3)

 

[bellman101]

I still don't see where the age comes into it, but you can get the male/female lookup in a number of ways. here's one:

=VLOOKUP(I194,OFFSET('Grip Strength'A2:C77,0,4),3)

I think you might need ',False' or ',0' after the 3 for an exact match, but not sure.

 

[Brent R Janetzki]

I still don't see where the age comes into it, but you can get the male/female lookup in a number of ways. here's one:

=VLOOKUP(I194,OFFSET('Grip Strength'A2:C77,0,4),3)

I think you might need ',False' or ',0' after the 3 for an exact match, but not sure.

The table in the vlookup consists of a set of average grip strength(s) for a persons age and gender. So I need the formula to say if the person I female then go to the female vlookup table and if they are aged say 25, the return the value of a female aged 25, if they are male then go to the male vlookup table and return the male aged 25 value. Does that make sense?

 

[bellman101]

Well it makes sense, but I still don't see how age affects the formula, unless it is in I194, but I think you said B-something. The formula I gave in my last post was incomplete. Apologies for that, sleepy.

It should have been:

=VLOOKUP(I194,OFFSET('Grip Strength'A2:C77,0,IF(B127="Male",4,0)),3)

And as I said before, I would consider adding ',0' between the 3 and the closing bracket.