# If, and and vlookup statements in one formula

#### Brent R Janetzki

##### New Member
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.

#### bellman101

##### Board Regular
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

##### New Member
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

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

##### Board Regular
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

##### New Member
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

##### Board Regular
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.

1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...