If, and and vlookup statements in one formula

Brent R Janetzki

New Member
Joined
Mar 11, 2017
Messages
13
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

Board Regular
Joined
Aug 20, 2012
Messages
245
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
Joined
Mar 11, 2017
Messages
13
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

Board Regular
Joined
Aug 20, 2012
Messages
245
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
Joined
Mar 11, 2017
Messages
13
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
Joined
Aug 20, 2012
Messages
245
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top