Log In
Register
Home
May 11, 2019
Offline
Hi!
I am trying to get an solution out of 4 possible outcomes to make a calculator. I tried a few IF(And functions, but Im not sure if this is the best way to attack this.
So as per the table below,
if its a Yes on EI Docs, and its a Yes in UA, then the Fee will be 25
if its a No on EI Docs, and its a No in UA, then the Fee will be 25
etc..
| EI DOCS | UA | Fee |
| Yes | Yes | 25 |
| No | No | 45 |
| No | Yes | 26 |
| No | No | 46.8 |
See Answer
October 5, 2010
Offline
Hi Robert,
The 2nd and 4th conditions are No in both El Docs and UA, so how do we distinguish the difference between them to know that one has a fee of 45 and the other has a fee of 46.8?
Also you write that 'if its a No on EI Docs, and its a No in UA, then the Fee will be 25' but the table has a different fee.
Regards
Phil
May 11, 2019
Offline
Hi Phillip,
Thanks for the reply. Youre right, I had a typo, the table should be as below:
| EI DOCS | UA | Fee |
| Yes | Yes | 25 |
| Yes | No | 45 |
| No | Yes | 26 |
| No | No | 46.8 |
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Based on the provided example I would use DGET for this. You need to change the layout of your ”input form” to get it to work.
Example: In cells B7 and C7 I have the same headers as in your lookup ”table” (cells G17 to I21, ie El DOCS and UA. In cells B8 and C8 we have the ”Yes” and ”No” dropdown lists and in cell D10 we have the below formula.
=DGET(G17:I21,3,B7:C8)
If you intend to add more to this ”form” then perhaps the DGET function is not the most optimal.
Answers Post
July 5, 2019
Offline
Another option is to add a "helper" column to your table, using formula:
=G24&H24
(and copy this formula down the table)
And then use this formula to get the answer (this assumes the formula above you put in range F24:F27):
=INDEX(I24:I27,MATCH(D7&D9,F24:F27,0))
1 Guest(s)
