If, and and vlookup statements in one formula

Brent R Janetzki

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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top