Hi,
Hope everyone is fine there. I am using Microsoft Office 2021 LTSC v2108 Build 14332.20481. I have only two sheets only everything is working fine but sometimes INDEX MATCH picking up wrong/duplicate values it using with excel table? Why?
But when we do it manually formulation i mean without excel excel table it works fine. Why? Attached file is easily understandable please cooperate with me in this matter. I also concern/post the same problem on Microsoft Tech community.
Thanks
Last edited by greatinfoteam; 05-15-2023 at 05:14 AM.
Please update your forum profile with your new version of Excel.
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.
What is the formula you are using in O6 when you use a range and NOT a table? Please provide it without the table references.
okay thanks
Please update your profile, as requested.
So what is the formula WIHOUT the table references, please? The one that works.
for example in Qty column i used tis formula =INDEX(Master!E5:E18,MATCH('Invoices Detail'!N18,Master!C5:C18,0)) and it is working fine.
Thanks - I'll have another look.
Did you understand the request to update your profile?
Please provide a workbook that shows what you want to achieve. Mock up the expected results MANUALLY and explain what the objective is.
Last edited by AliGW; 05-15-2023 at 06:08 AM.
I just updated my profile about office version. Here is the sample image and excel file i used formula without table and getting exact matching results entered in Master sheet.
Last edited by greatinfoteam; 05-15-2023 at 06:42 AM.
The workbook does NOT show expected results.
Provide a workbook with a working range and a non-working table so that we can compare them.
Just repeating what you have said already instead of answering the question won't move you forward. I would like to see BOTH the working range AND the non-working table, please.
Last edited by AliGW; 05-15-2023 at 07:32 AM. Reason: Typo fixed.
Hi,
In the Match function, you closed the parenthesis incorrectly
Formula:
Please Login or Register to view this content.
The correct position:
Formula:Please Login or Register to view this content.
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
Actually what you mean i just got it just compare two formulas:
1st one is: =IFERROR(INDEX(Main[Style No.],MATCH($N17,Main[PO/SO No.]),0),"")
2nd one is: =INDEX(Master!D5:D18,MATCH('Invoices Detail'!N18,Master!C5:C18,0))
so the 1st one is not getting exact results at somehow but 2nd one is giving the exact results. I am just saying about looking into the formula only the sheet is same in which 1st type of formula is not working properly but when we use same formula in same workbook in same sheet in same column it give us exact matching criteria? So my concern is why INDEX MATCH is not working properly when we use it like this =(INDEX(Main[Style No.],MATCH($N17,Main[PO/SO No.]),0) but if we give it ranges manually like this =INDEX(Master!D5:D18,MATCH('Invoices Detail'!N18,Master!C5:C18,0)) it works fine.
As i said i never created two different separated workbooks for experiment it is the only workbook.
Sorry if i still could not provide you the proper information.
So, as Diana said, check the parenthesis:
1st one should be: =IFERROR(INDEX(Main[Style No.],MATCH($N17,Main[PO/SO No.],0)),"")
2nd one is: =INDEX(Master!D5:D18,MATCH('Invoices Detail'!N18,Master!C5:C18,0))
OMG
it was working from start so i did not notice this mistake thanks a lot Diana Tanase.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 all those who offered help.
THANKS a lot AliGW you too as you really respond so quickly every time.
You're welcome.
In future, please include EXPECTED RESULTS as well as non-working formulae.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks