Hello Sir/Madam,
In sheet 1, Column A contains systems such as 0-20-A0-P01, 0-20-A0-P02, P03, P04, and so on. In other sheets, Column A in sheet 2 has the same systems with many duplicates. In the same sheet 2, somewhere Column D or Column E have values like "A", "B", "C" with duplicates.
I need to use a VLOOKUP formula in sheet 1 to find out how many "A" are available with respect to 0-20-A0-P01, and how many "B" are with respect to 0-20-A0-P01, and so on. The same applies to other systems like 0-20-A0-P02, to determine the count of A, B, C, and so on.
I have tried using VLOOKUP and COUNTIF but couldn't achieve the desired result. I would appreciate any suggestions, please.
From Germany, Munich
Acknowledge(0)
Amend(0)
In sheet 1, Column A contains systems such as 0-20-A0-P01, 0-20-A0-P02, P03, P04, and so on. In other sheets, Column A in sheet 2 has the same systems with many duplicates. In the same sheet 2, somewhere Column D or Column E have values like "A", "B", "C" with duplicates.
I need to use a VLOOKUP formula in sheet 1 to find out how many "A" are available with respect to 0-20-A0-P01, and how many "B" are with respect to 0-20-A0-P01, and so on. The same applies to other systems like 0-20-A0-P02, to determine the count of A, B, C, and so on.
I have tried using VLOOKUP and COUNTIF but couldn't achieve the desired result. I would appreciate any suggestions, please.
From Germany, Munich
Acknowledge(0)Amend(0)
Vlook up won’t help, as it will pick the first A or B or C corresponding value in sheet 2, since you say there are duplicates. Try Pivot Table, i guess that will help resolve your problem.
Acknowledge(0)Amend(0)
Acknowledge(0)Amend(0)
Can you specify what your main motive is? Is it:
a) To get results, i.e., counting how many A, B, C...
or b) To get results only using Vlookup and countif?
If your motive is option (a), then please find the attached Excel sheet; otherwise, reply back, and I will try to work on option (b). However, I agree with nkumar11 that Vlookup will only give the first encounter. You can also try a Pivot Table as suggested by nkumar11.
Let's see if any experts can solve it using Vlookup along with a combination of other functions because Excel is a vast area, and any combination could give you a result!
From India, Gurgaon
Acknowledge(0)Amend(0)
1a) To get results, i.e., counting how many A, B, C...
or b) To get results only using Vlookup and countif?
If your motive is option (a), then please find the attached Excel sheet; otherwise, reply back, and I will try to work on option (b). However, I agree with nkumar11 that Vlookup will only give the first encounter. You can also try a Pivot Table as suggested by nkumar11.
Let's see if any experts can solve it using Vlookup along with a combination of other functions because Excel is a vast area, and any combination could give you a result!
From India, Gurgaon
Acknowledge(0)Amend(0)
HI Use countifs and then remove duplicates.find he excel sheet and let me know if there is any problem thanks
From India, Pune
Acknowledge(0)Amend(0)
From India, Pune
Acknowledge(0)Amend(0)
Dear All,
I found the solution for my query by using the sumproduct function, which is used to check two arrays in a spreadsheet. With the assistance of one of my queries. However, all suggestions are also very valuable to me for future updates in my work process. I would really appreciate it if you could come forward to assist me. Thank you very much for all your help. If I have any queries, I will let you know with clear pictures. Thank you once again.
From Germany, Munich
Acknowledge(0)Amend(0)
I found the solution for my query by using the sumproduct function, which is used to check two arrays in a spreadsheet. With the assistance of one of my queries. However, all suggestions are also very valuable to me for future updates in my work process. I would really appreciate it if you could come forward to assist me. Thank you very much for all your help. If I have any queries, I will let you know with clear pictures. Thank you once again.
From Germany, Munich
Acknowledge(0)Amend(0)Looking for something specific? - Join & Be Part Of Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Vlookup in Excel.xls
23_excel funaction and data analysis tool.pdf