Dear All
I have attached an excel showing a problem where I am trying to match a value in one sheet with another sheet that contains data. The problem I have is that in the Data sheet, the vlaue I am trying to find and get a result for has multiple entries. Anyone can please advsie how to possibly solve?
Thank you for your kind help.
Vba1234
Administrative Note:
Is your forum profile showing the version of Excel that you need this to work for?
Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.
Thanks.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Where is the cell where you have issues?
Which is the source data sheet?
What is your expected result (value) and why?
Where you DO have multiple results that are possible... what is the RULE that you use to decide WHICH one to return?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Dear AliGW and Glenn
Thank you for your reply. Sorry for any problems caused by my thread.
My Excel is part of Microsoft 365 Apps for Business.
Regarding my thread, my sheets AIR and SEA are the ones that I get sent data and I need to try to match the information with the data I export from anothe program we use an dcreate two sheets - Airline Handling and Customs Clkearnce. In the original Air sheet, I add Columns - Customs Clearnce-DGL, Airline Handling and Collection. In SEA I add columns Quantity of invoice and Date. These are where I need the data from sheets Airline Handling and Customs Clearnce would be taken from. If you look at the Sheet Airline Handling you can see the occurrence of Job DGAI7887 coming up three times when i search. If i use vlookup it will only give me the first entry it matches, but this is incorrect as the result in this case should the third entry. This also goes for Customs Clearance- The same problem -I have highlighted in different colours the same job appearing more than once.
PS I have addedd a comment on sheet AIR as an example
You did not answer all of my question.Originally Posted by vba1234
WHY should it be the 3rd one... and not the first, or the second?
Please update your forum profile, as requested.
Assuming you want to return the largest value:
Customs Clearance DGL:
Formula:
=LARGE(FILTER('Customs Clearance'!M:M,'Customs Clearance'!A:A=Air!B2),1)
Airline Handling:
Formula:=IFERROR(LARGE(FILTER('Airline Handling'!J:J,'Airline Handling'!A:A=Air!B2,),1),"Not found")
Assuming that the answer to my Q is "the last one", try:
=TAKE(FILTER('Customs Clearance'!$M$2:$M$100,'Customs Clearance'!$A$2:$A$100=Air!B2),-1)
Hi All
Thank you. I will look at your replies and try these out on the on the SEA sheet.
vba1234
Hi All
I have had a chance to check with your replies and can say that this is working so will need to close this.
Thank you again for your kind help.
Have a great day.
vba1234
The two suggestions were VERY different. As a matter of interest... which one worked?
In any event, you're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks