What if statement

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
How do I write the following what if statement:

If Z3=4 & V3=1, then D28/4 otherwise nothing.

Thanks for your help as always.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
Hi Mudface, thanks for your help. I have an even more complex WHAT IF scenario...I hope you (or someone else) can help me with it. If a simple WHAT IF statement will not work, maybe someone could suggest a macro that will do the trick, here goes:

If Z3=4 and V3=1 then d28/4
or if Z3=4 and V3=2, then d28/5
or if Z3=4 and V3=3, then d28/6
or if Z3=3 and V3=1, then d28/7
or if Z3=3 and V3=2, then d28/8
or if Z3=3 and V3=4, then d28/9
or if Z3=2 and V3=1, then d28/10
or if Z3=2 and V3=3, then d28/11
or if Z3=2 and V3=4, then d28/12
or if Z3=1 and V3=2, then d28/13
or if Z3=1 and V3=3, then d28/14
or if Z3=1 and V3=4, then d28/15

That's it...looks complex to me, but I hope its not that complex to the experts.

Thanks in advance.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi,

three methods here, all of which rely on having a lookup table to pull the values from (because what you are essentially doing is a multi-key lookup):
Book8
ABCDEFG
1ZVDividebyZVd28
241432100
3425
4436Results
5317DGET12.5
6328ArrayIndex12.5
7349Sumproduct12.5
82110
92311
102412
111213
121314
131415
Sheet1


1) Use Dget.

=G2/DGET(A1:C13,3,E1:F2)

Probably the easiest to write, but you do need to make to make sure your data is set up right - see the help file for database functions for the details.

2) Array entered index function:

=G2/INDEX(C2:C13,MATCH(F2,IF(A2:A13=E2,B2:B13),0))

This needs to be entered using control + shift + enter, not just enter. Excel will add curly brackets round the formula if done right.

3) Sumproduct.

=G2/INDEX(C1:C13,SUMPRODUCT((A1:A13=E2)*(B1:B13=F2)*(ROW(A1:A13))),1)

I don't suggest you use this one as it will only work if you can guarentee that your lookup combinations are unique. Posted more for reference than anything.

See here:

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2&3

for more details / complex examples & post back if you need.

Paddy
This message was edited by PaddyD on 2002-09-05 19:50
 

Forum statistics

Threads
1,144,127
Messages
5,722,634
Members
422,450
Latest member
Springbok

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
Top