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 |
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
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 |
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.
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))
Thanks Anders,
This worked just fine.
