Forum Discussion

marcogallo99's avatar
marcogallo99
Copper Contributor
Nov 21, 2020

vlookup formula problem

Hi everyone,   I have a question about the vlookup formula (I guess). I would like to link two formulas (like IF and VLOOKUP, or IFNA and VLOOKUP, I'm not sure). Here's my needs: in column B I ha...
excel
Formulas and Functions
marcogallo99's avatar
marcogallo99
Copper Contributor
Nov 21, 2020

NikolinoDE

I'm so sorry, I'm Italian and my English is not so good ahahah...

 

I attach an example table.

 

As you see, I have some names in column C, but not all of them have a number in column F.

I need to count how many times there is a name in C with a number in F (there's no way a name can figure once with a number and then without a number, every single name has ALWAYS or NEVER a number)

 

Here is an example, I created (in another spreadsheet) a list with an example of all the names I need, and these will be the results:

 

MATT    0

GEORGE   0

KEVIN   2

LUCAS    1

ANTHONY     0

MICHAEL      1

PETER     0

 

I'm not even sure which formulas I need, I thought about an IF and a VLOOKUP but it can be everything you want...

 

Thank you so much

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 21, 2020

marcogallo99 

As variant that could be

with

=LET(
    namesRange, C2:C14,
    numbersRange, F2:F14,
    uniqueNames, UNIQUE(namesRange),
    counts, COUNTIFS(numbersRange,">0",namesRange,uniqueNames),
    rawResult, IF({1,0},uniqueNames,counts),
    Result,FILTER(rawResult,uniqueNames<>0),
Result)

Resources