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.
Using VLOOKUP for Counting Values
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)
Using VLOOKUP for Counting Values
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)
Understanding Your Main Motive
Can you specify what your main motive is? Is it:
- a) To get results, i.e., counting how many A, B, C...
- 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!
Regards
From India, Gurgaon
Acknowledge(0)Amend(0)
1Can you specify what your main motive is? Is it:
- a) To get results, i.e., counting how many A, B, C...
- 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!
Regards
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)
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, I was able to resolve the issue. 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.
Regards
From Germany, Munich
Acknowledge(0)Amend(0)
Regards
From Germany, Munich
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Functions Dictionary.xls
23_excel funaction and data analysis tool.pdf