Hi,
I have a table with 3 categories "Hemisphere", "Country" and "Town".
I have a formula that will bring back the first correctly identified Hemisphere based on the Town. However I need a formula that will bring back the Hemisphere based on Town and Country. How do I do this please?
I have included the excel workbook with the formulas I am using.
Thanks!
Last edited by awoolfe; 01-20-2021 at 02:48 AM. Reason: Update excel file
Hi,
What should be the right answers? Can you please fill in the table manually?
Sure. I've uploaded it with correct answers.
For the country, which I am sure you will be able to adapt for hemisphere yourself:
=IFERROR(LOOKUP(2,1/((DataSet[Hemisphere]="Northern")*(DataSet[Town]=D3)),DataSet[Country]),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thanks @AliGW for helping me with the formula:
=IFERROR(LOOKUP(2,1/((DataSet[Hemisphere]="Northern")*(DataSet[Town]=D3)),DataSet[Country]),"")
This worked in my example excel sheet, however because I don't fully understand it I am having trouble translating it to my actual excel sheet. I searched youtube but all the videos were on index and match.
I am wondering what the "2" and "1" and "/" stand for? I am wondering if you could write out the formula in simple words and I will try it again?
Thanks
Don’t worry about the start of the formula, because you don’t need to change it. I will post a link to a clear explanation of it later.
All you need to know are that the criteria columns are in the first part of the formula:
((Lookupcolumn1=Lookupcriterion1)*(Lookupcolumn2=Lookupcriterion2))
This establishes the ‘filters’, if you like, to hone in on the correct row - in your case you need to match the hemisphere and the town to ensure that the correct country is found. The value you want to return is the lookup column at the end of the formula.
What is happening when the formula executes is that Excel is creating matrices of true/false returns, and the row on which it finds the intersection of two trues is where it will find the correct answer. Use Evaluate Formula on the Formulas ribbon to step through the formula in the sample workbook to see how it is working. You can add more criteria if you have extra columns that need matching.
Thanks @AliGW !! Definitely user error. I did get it to work on my excel sheet. In terms of the formula, I understand the "1/" to create an error. I don't understand the "2" for the lookup value. I'll have a read of the link when you post it.
You don't need to - any number above 1 will do!
Here's that link to an explanation of a similar formula - the bit about the 2 and 1 holds true for your formula, too: https://www.excelforum.com/excel-for...ml#post3398950
Thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks