If + Vlookup function

[maratonomak]

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 !!

 

[sericom]

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]

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

	A	B	C	D	E	F	G	H	I
1	Make	Model	Disp	Hwy MPG	City MPG		Criteria
2	Acura	RSX	1.8L	30	26		C1	C2	C3
3	Acura	RSX	2L	27	21		Acura	Integra	2L
4	Acura	RSX	2.2L	25	20
5	Acura	Integra	1.5L	35	30		Result Hwy MPG:	30
6	Acura	Integra	1.8L	33	30		Result City MPG:	26
7	Acura	Integra	2L	30	26
8	Acura	NSX	2.5L	27	22
9	Acura	NSX	2.8L	25	21
10	Acura	NSX	3L	20	15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula 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)))} <thead> </thead><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 Name Refers 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 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[maratonomak]

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

	A	B	C	D	E	F	G	H	I
1	Make	Model	Disp	Hwy MPG	City MPG		Criteria
2	Acura	RSX	1.8L	30	26		C1	C2	C3
3	Acura	RSX	2L	27	21		Acura	Integra	2L
4	Acura	RSX	2.2L	25	20
5	Acura	Integra	1.5L	35	30		Result Hwy MPG:	30
6	Acura	Integra	1.8L	33	30		Result City MPG:	26
7	Acura	Integra	2L	30	26
8	Acura	NSX	2.5L	27	22
9	Acura	NSX	2.8L	25	21
10	Acura	NSX	3L	20	15

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula 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 Name Refers 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]

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.