Log In
Register
Home
Active Member
July 4, 2021
Offline
A1 cell is "EUR", B1 cell is "Tax".
A2 cell is "150,00EUR", B2 cell is "18,50EUR"
A3 cell is "150,01EUR", B3 cell is "37,00EUR"
A4 cell is "1.000,00EUR", B4 cell is "37,00EUR"
A5 cell is "1.000,01EUR", B5 cell is "99,00EUR"
D1 cell is 607,25EUR.
F1 cell need to be my result cell for the bellow task...
I need to create a one long sausage Excel formula here, and I think that I am requiring the "IF and OR" nested function combination:
IF the price in D1 cell is lower or equal A2 cell, then the Tax will be B2 cell;
IF the price in D1 cell is equal or higher A3 cell, then the Tax will be B3 cell;
OR, IF the price in D1 cell is lower or equal A4 cell, then the Tax will be B4 cell (still a 37,00EUR value);
IF the price in D1 cell is equal or higher then A5 cell, then the Tax will be B5 cell.
I spend long time to figured out, but I obviously make somewhere serious mistake, cannot get it simply to work.
Please help and advice, this is for Up to date Microsoft Excel 2019 C2R on Windows 10...
Thank you in advance and best regards!
See Answer
July 16, 2010
Offline
Hi Bruno,
Welcome to our forum! I did a tutorial on this just this week. Please watch the IF statement video here which explains that you should actually be using a lookup (VLOOKUP or XLOOKUP).
Hope that helps. If you're still stuck, come back and share your file so we can see what the issue is.
Mynda
Answers Post
Active Member
July 4, 2021
Offline
Thank you dear Mynda, and thank you for the welcoming! 🙂
O, that means that I did not even pick up the right formula, and I was sure that it is an IF, and or IF + OR mixture! 🙂
OK, I will most surely watch your recommended video, and try to fix things with VLOOKUP or XLOOKUP.
If I will not, I will please ask the assistance again here.
Once more thank you and best regards!
Active Member
July 4, 2021
Offline
Well, totally simple VLOOKUP do the all the magic trick here, and I cannot believe it,
that I did not get it before even by myself, and that I am using the completely wrong formula approach!
I am just reporting this now for helping others here on this great forum, because I have a bingo solution! 🙂
I also slightly change my table to be more a correct for getting the results from the VLOOKUP formula.
Now on this way, the VLOOKUP formula can even evaluate the table values bellow the initial set of 150,00EUR.
So now, the VLOOKUP formula can give me even a results when I have for e.g 1€ only, etc..:
A1 cell is "EUR", B1 cell is "Tax".
A2 cell is "0,1EUR"; B2 cell is "18,50EUR"
A3 cell is "150,00EUR", B3 cell is "18,50EUR"
A4 cell is "150,01EUR", B4 cell is "37,00EUR"
A5 cell is "1.000,00EUR", B5 cell is "37,00EUR"
A6 cell is "1.000,01EUR", B6 cell is "99,00EUR"
D1 cell is 607,25EUR.
F1 cell need to be my result cell for the bellow task...
And for the above given Table, the F1 cell VLOOKUP result is:
=VLOOKUP(D1;A1:B6;2)
Once more thank you for the correct hint formula, dear Mynda!
Till next time on this great forum, cheers to all here! 🙂
1 Guest(s)
