In practice, there are many reasons why you might see this error, including: Step 1: Now enter the formula in cells F4 and J4. Now, we will count the names and try to match them. Let’s assume we have a dataset of employees with their names and ID. Read more: VLOOKUP to Search Text in Excel To be specific we will see if the total count of any element is zero then what will be returned by COUNTIF and VLOOKUP functions. In this section, we will try to find out how COUNTIF and VLOOKUP functions handle searching operations. COUNTIF vs VLOOKUP for Determining If a Value Exists Step 3: Now enter another name that has less than 4 percentagesģ. Step 2: Enter any name in cell D17 and press Enter Otherwise, print the average marks with the help of the VLOOKUP function. If the result is true then print #NA using NA() function.

Lastly using the IF function we are checking if the number of percentages is less than 4 or not.

INDEX($C$4:$H$13, MATCH(D17,$B$4:$B$13,0),0),”>0″)0″)<4 using this we are counting if not zero cells have less than 4 percentages or not.

To learn more about this function visit this link

MATCH(D17,$B$4:$B$13,0) this part is looking for the matched Names with our entered name.

Here along with VLOOKUP and COUNTIF, we are using IF, INDEX, MATCH functions also.

That’s means if any student has less than 4 percentage then we will simply return #NA! Otherwise, we will return the average percentages of the grades. Now our concern is to find the average percentages of all the grades if there are at least 4 percentages of the grades. Let’s have a dataset of student marks for each course (like 6 courses).

Calculate Percentages Using VLOOKUP and COUNTIF functions Step 2: Now enter any name in the C18 cellĢ.

For more information about the COUNTIF function please visit this link.

After that COUNTIF function will search the ID which is got by the VLOOKUP function in the table range F5:K14.

If you want to learn more about this VLOOKUP function, visit this link.

2 is for two columns (Name and ID) and the last 0 is for an exact match. Then B5:C14 is the range where we will search the data. C18 in this cell we will put the input data.

First look at the VLOOKUP function VLOOKUP(C18, B5:C14,2,0).

Do vlookup in excel