If + Vlookup function

maratonomak

New Member
I'm trying to make the function below to work :

=IF(And(B1="Virginia",VLOOKUP(A2,Male!A2:C52,2),IF(A1="Male",VLOOKUP(A2,Male!A2:C52,2,FALSE),VLOOKUP(A2,Male!A1:C52,3,FALSE)).

Basically trying to get the price based on sex and age , which works with: IF(A1="Male",VLOOKUP(A2,Male!A2:C52,2,FALSE),VLOOKUP(A2,Male!A1:C52,3,FALSE)).
But I need a separate IF to look at another cell as well: IF(B1="Virginia",VLOOKUP(A2,Male!A2:C52,2). So, if B1="Virginia" the value should be Male!A2:C52.

Any help will be appreciated !!

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

sericom

Well-known Member
Does this do it?

=IF(AND(B1="Virginia",A1="Male"),VLOOKUP(A2,Male!A2:C52,2),VLOOKUP(A2,Male!A1:C52,3,FALSE))

That says if B1 is Virginia AND A1 is Male, then grab the value in column B, else grab the value in Column C.

mick0005

Active Member
I am not sure if I understand your data structure exactly. Are you just looking for a multiple criteria lookup that uses the same index array as the result?

If so, why not do something like this (excuse the example, but I already had this built in my personal workbook). This is a 3 criteria index match, however you could add as many or as few as 2 criteria. Be sure to enter with a CTRL+SHIFT+ENTER because this is an array formula.

Excel 2010
ABCDEFGHI
1MakeModelDispHwy MPGCity MPGCriteria
2AcuraRSX1.8L3026C1C2C3
3AcuraRSX2L2721AcuraIntegra2L
4AcuraRSX2.2L2520
5AcuraIntegra1.5L3530Result Hwy MPG:30
6AcuraIntegra1.8L3330Result City MPG:26
7AcuraIntegra2L3026
8AcuraNSX2.5L2722
9AcuraNSX2.8L2521
10AcuraNSX3L2015

</tbody>
Sheet3

Array Formulas
CellFormula
H5{=INDEX(Hwy,MATCH(1,IF(Make=G3,IF(Model=H3,IF(Disp=I3,1)),0)))}
H6{=INDEX(City,MATCH(1,IF(Make=G3,IF(Model=H3,IF(Disp=I3,1)),0)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
City=Sheet3!\$E\$2:\$E\$10
Disp=Sheet3!\$C\$2:\$C\$10
Hwy=Sheet3!\$D\$2:\$D\$10
Make=Sheet3!\$A\$2:\$A\$10
Mode=Sheet1!\$G\$2:\$G\$41
Model=Sheet3!\$B\$2:\$B\$10

</tbody>

<tbody>
</tbody>

maratonomak

New Member
I am not sure if I understand your data structure exactly. Are you just looking for a multiple criteria lookup that uses the same index array as the result?

If so, why not do something like this (excuse the example, but I already had this built in my personal workbook). This is a 3 criteria index match, however you could add as many or as few as 2 criteria. Be sure to enter with a CTRL+SHIFT+ENTER because this is an array formula.

Excel 2010
ABCDEFGHI
1MakeModelDispHwy MPGCity MPGCriteria
2AcuraRSX1.8L3026C1C2C3
3AcuraRSX2L2721AcuraIntegra2L
4AcuraRSX2.2L2520
5AcuraIntegra1.5L3530Result Hwy MPG:30
6AcuraIntegra1.8L3330Result City MPG:26
7AcuraIntegra2L3026
8AcuraNSX2.5L2722
9AcuraNSX2.8L2521
10AcuraNSX3L2015

<tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
H5{=INDEX(Hwy,MATCH(1,IF(Make=G3,IF(Model=H3,IF(Disp=I3,1)),0)))}
H6{=INDEX(City,MATCH(1,IF(Make=G3,IF(Model=H3,IF(Disp=I3,1)),0)))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
City=Sheet3!\$E\$2:\$E\$10
Disp=Sheet3!\$C\$2:\$C\$10
Hwy=Sheet3!\$D\$2:\$D\$10
Make=Sheet3!\$A\$2:\$A\$10
Mode=Sheet1!\$G\$2:\$G\$41
Model=Sheet3!\$B\$2:\$B\$10

<tbody>
</tbody>

<tbody>
</tbody>

Thank you !

mick0005

Active Member
Thank you !

So that is what you needed? I couldn't exactly tell from your question if you actually wanted to have 2 seperate arrays to pull the result from, or if you just had multiple criteria to run against the same lookup and output from a single array.

Happy it helped.

Replies
5
Views
742
Replies
4
Views
280
Replies
6
Views
2K
Replies
3
Views
199
Replies
3
Views
504

1,195,669
Messages
6,011,059
Members
441,580
Latest member
BornholmerBjarne

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.

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

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