vlookup formula problem

Copper Contributor

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 have a list of names, in column F I have some numbers. But not every name has a number in F column. I need the formula to display me a YES if there's a number, otherwise a NO.

 

The argument I'm trying to create is:

 

if VLOOKUP finds a value in B5, and F5 is blank, then return this; if VLOOKUP finds a value in B5, and F5 is NOT blank, then return this; if VLOOKUP does NOT find a value, then return this.

 

Here's another way:

 

if VLOOKUP finds a value in B5, and F5 is blank OR if VLOOKUP does NOT find a value, then return this; if VLOOKUP finds a value in B5, and F5 is NOT blank, then return this.

 

Do you guys have any idea to help me?

 

Thank you so much

10 Replies

@marcogallo99 

I didn't really understand what you're up to.

but here is an example of a "VLOOKUP, IF; AND" combination ,,,,,

= IF (AND (VLOOKUP ($ A3; Sheet2! $ A: $ I; 9; 0)> = (G3 + I3); (N3 <0)); "Order"; IF (AND (G3> = L3; I3> 0); "Cancel"; ""))

maybe you can adapt it to your needs!

 

otherwise send your table with an example (w/out sensitive data)!

 

additional infos:

IF function – nested formulas and avoiding pitfalls

https://support.microsoft.com/en-us/office/if-function-%e2%80%93-nested-formulas-and-avoiding-pitfal...

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@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

@marcogallo99 

If I understand correctly the picture of you and the translation, I will send you this file as a solution.

 

Sarei felice di sapere se potessi aiutare.

 

Nikolino

So di non sapere niente (Socrate)

* Segna e vota gentilmente questa risposta se aiuta, per favore, poiché sarà utile per più membri della Comunità che leggono qui.

@marcogallo99 

As variant that could be

image.png

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)

@NikolinoDE 

 

Thank you very much for interesting in my problem, but unfortunately it's not what I need (it's not your fault obviously)...

 

I'll try to explain WHY I need all this.

I have a school of motorbike, and it counts like 800 students. Everyone of these 800 can come whenever they want, once, twice or even more, but everyday of lesson, we can accept only the first 100 who subscribe. I have a list of all the 800 names, but of course not all of them come to EVERY SINGLE day of lessons! So I need to count how many times they came.

How do we know if they came in a specific day? Well of course, we can see his name among the 100 subscribers, but we will count him only IF he has a price next to him (if there is not a price, it means that he subscribed but he didn't come, so we don't need to count him).

 

I attach an example table with the list of all the names and two days of lessons, and the result I need from this formula.

 

I saw that you put a formula in column C that gives the sum of all the values of a single name (ex. Kevin has 2 values "50", so next to Kevin I will see 50+50=100, Lucas has "25" and "70" so next to Lucas I will always see 25+75=95, and so on).

 

But I don't need to see the sum of the values. It doesn't matter for me if they have 50, 25, 70 or whatever. I need just to see how many times they have a value!

 

Maybe the solution from @Sergei Baklan fits more my needs, but I'm not so confident with those "xlfn" and "xlpm" formulas... Is there another easier way to solve it?

 

Thank you so much guys for your effort!!!!

@marcogallo99 

Here is a simple pivot table solution. It requires a change of structure.

 

@marcogallo99 

Marco,

Ecco un piccolo esempio nel Foglio 2.

Premere il tasto destro del mouse sui campi del giorno e viene visualizzato "1",

ripetere "1" scompare.

Password VBA: "1234"

Aggiunge automaticamente i giorni accanto ai nomi dei partecipanti.

 

Potresti ampliarlo e migliorarlo come desideri ... poi sarebbe un lavoro commissionato.

Non faccio lavori su commissione a meno che non ci sia la vera pizza napoletana ... sto scherzando :).

 

Per informazioni: l'utente non parla italiano, questa è una traduzione da un programma software.

 

Sarei felice di sapere se potessi aiutare.

 

Nikolino

So di non sapere niente (Socrate)

* Segna e vota gentilmente qualsiasi risposta se aiuta, per favore, poiché sarà utile per più membri della Comunità che leggono qui.

@marcogallo99 

If you have xlfn that means your version of Excel doesn't support dynamic arrays and these formulas won't work.

In general basis formula could be relatively simple, e.g. in C11

=IF($B11="","",COUNTIFS(J$7:J$19,$B11,K$7:K$19,">0"))

and drag it down till end of the list. For the next day copy this formula to the right, adjust ranges and copy again till end of the list, etc.

 

That will be still a lot of manual work and the main issue is how you structure the data and how you keep it.

For example, in main list you have name "Michael " (with spaces at the end) and in the date list "Michael". Extra spaces could be removed by formulas, but that could be other misprints of you add names manually, like "Michal".  Thus you need to check all result which is not far from manual calculations. Solution could data validation drop down lists or another data structuring to use every name only one.

 

Another issue with the sample is the need to adjust formula for each day since there is no formal logic where to find data for each day. If such logic exists formulas could be adjusted to only copy/paste them without adjustments for each date.

 

Thus the solution depends on actual data structuring and are you ready to change it or not. We don't know your business and don't know what else do you have in the sheets and how else you use the information. 

 

From formulas point of view most reliable will PivotTable as @Detlef Lewin suggested, but that all depends on how deep the data restructuring will be.

 

Perhaps your actual data looks totally different and PivotTable works on it as it is.

@NikolinoDE 

L'ho stretto un po 'e l'ho tagliato in italiano ... niente di meglio da fare :))

 

Non è vero ... aveva qualcosa di meglio da fare ... ma non volevo farlo .... sognavo la pizza :)))

Sto solo scherzando, per favore non prenderlo sul serio :)

 

Il file (VBA Pass: 1234):

Ora puoi navigare nel file fino al mese e tornare indietro con il semplice tocco di un pulsante.

È possibile cercare il nome se sono già stati inseriti molti nomi.

Una riga aggiuntiva viene aggiunta automaticamente dopo ogni immissione del nome.

 

Ti auguro una buona giornata / notte con tanta salute, gioia e amore.

 

Grazie per la pazienza e il tempo.

 

Nikolino

So di non sapere niente (Socrate)

* Segna e vota gentilmente qualsiasi risposta se aiuta, per favore, poiché sarà utile per più membri della Comunità che leggono qui.

@Sergei Baklan 

@NikolinoDE 

@Detlef Lewin 

 

Dear @Sergei Baklan , you successfully found the perfect solution for me. This was right what I was looking for, a simple "magic formula" that I can copy, paste and adapt wherever I want, and that updates automatically when I update the original data (if later I add a name, the formula will count it without my action).

 

I won't have too much manual work, because my school only opens 2/3 weekends per month (it's for children, and they have school during the week), so there won't be 365 formulas to adjust but only 60 per year more or less... And I trust my hands there won't be misprints during the typing... ;)

 

I checked the solutions from @NikolinoDE and @Detlef Lewin , they're also brilliant and I'll be inspired by them, but I'm not too confident with PivotTable and VBA language... But I'm going to try to learn these two functions becaue I'm sure my work will be much more faster with them!

 

Anyway, @NikolinoDE how did you register the Macro for the "Search name" button on your new file? I tried to start the record, open the "Find" popup, stop the record, but it doesn't work... (great Italian btw!)

And the buttons to jump directly to the months are a great idea!

 

I really didn't expect so much help from you guys and from this community. If someday you will come to North Italy, please contact me and I'll be happy to offer you a free motorbike lesson for you and your sons! (www.juniormotoschool.it). 

...Or a Pizza if you prefer :):)