Excel Data Analysis Techniques: VLOOKUP, COUNTIF, and Pivot Tables Explained

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.

VLOOKUP won't help as it will pick the first A, B, or C corresponding value in sheet 2 since you mentioned there are duplicates. Try using a Pivot Table; I guess that will help resolve your problem.

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!

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.