VLOOKUP help - return value if two partial matches are met (I think??)

[monkeydan]

Hi all,

I am tearing my hair (what little I have left) out with this one.

I have a spreadsheet ("Professional Training MASTER") which contains historical training instances for a number of trainings.

We would like to reference this data in another spreadsheet via VLOOKUP but I cannot figure out how to do what I need it to!

The relevant course titles are as follows:

IGF Code Basic
IGF Code Basic and Tanker Fire Fighting
IGF Code Basic ONLINE
IGF Code Basic and Advanced
IGF Code Basic and Advanced ONLINE
IGF Code Advanced
IGF Code Advanced ONLINE
IGF Code Advanced & Simulated Bunkering Operations

etc.

If the course title contains "IGF Code" AND "Basic", then it should return data from that row

If the course title contains "IGF Code" AND "Advanced", then it should return data for that row.

The referenced row would therefore be the same if they completed the IGF Code Basic and Advanced course.

There are multiple other other course titles with the term "Basic" or "Advanced" in them, so I cannot just use *Basic* as the search term for the VLOOKUP (not that I can get that to work either!)

I combine the employee's personnel number and the course title to create a unique identifier.

This is where I am at right now, but the formula is not returning a value even though the employee has completed

=IF(VLOOKUP(C8&"IGF Code*",TRAINING DATA,5,FALSE)="*Basic*",VLOOKUP(C8&"IGF Code*",TRAINING DATA,10,FALSE),IF(VLOOKUP(C8&"IGF Code*",TRAINING DATA,5,FALSE)="*Advanced*",VLOOKUP(C8&"IGF Code*",TRAINING DATA,10,FALSE)

The VLOOKUP should be looking for the word "Basic" in column 5 (course title) and, if that is found, return data from column 10 (course status).

I also need the formula to include look for Basic and Advanced IGF Code and return blank (rather than #N/A) if no result is returned for either.

I am sure there is an easy way to do this, but I don't know what it is. I am reasonably good with Excel but not to the point where I can build complicated formulas from the ground up... generally I Google and try to adapt for my specific use case without always understanding the functions.

Thanks for any assistance anyone could provide

Dan

 

[MountainFog]

what version of excel are you using. If 365, i would use filter instead.

Where A1:A1000 is the range you want to return, and B1:B1000 are the training names

=FILTER(A1:A1000,(ISNUMBER(SEARCH("IGF CODE",B1:B1000)))*(ISNUMBER(SEARCH("BASIC",B1:B1000))))

 

[monkeydan]

Hi MountainFog,

Appreciate your reply, thank you.

Although we do use 365, anything we do in Excel also needs to be compatible across all the versions of Excel used within the team.

For whatever reason (our IT dept. I guess), I am running 2016 desktop version whereas most colleagues are running 2019.

As it's not possible to run macros in 365 / online version, we need to be able to open Excel in our own desktop versions to run macros.

I am not familiar with the FILTER function but have spent a while today searching online to see how it is used.

However I am struggling to see how your solution fits my particular use case (probably because I didn't explain it very well!)

The data being returned is for specific employees, but there does not appear to be a unique identifier in the FILTER formula suggested?

I added the formula to the sheet but it is not returning data for the specific employee personnel number in the adjacent cell,

Spreadsheet one is a list of employees. I need a VLOOKUP (or other solution) to check spreadsheet two ('Professional Training MASTER' referenced in my original post).
If an employee in spreadsheet one is listed in the Professional Training MASTER as having completed any training including the terms IGF Code and Basic, it needs to return data from that row

Hope that makes sense? I tried concatenating the personnel number with the "IGF Code" text in your FILTER formula as below, but it's giving me a #CALC error.

=FILTER(A1:A1000,(ISNUMBER(SEARCH(C8&"IGF CODE",B1:B1000)))*(ISNUMBER(SEARCH("BASIC",B1:B1000))))

 

[Fluff]

I am running 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[monkeydan]

Thanks and done

 

[MountainFog]

Can you post an example of your data? I'm not sure how the employee identifier is tied in. The #calc error means that it is not found.

Say the employee number in C8 is XXXXX

The function is now searching for XXXXXIGF CODE, which is why it isn't found.