VLOOKUP help....

Hi everyone, hopefully you can help me with a major headache i'm having at the moment.

I'm not really a heavy excel user but managed to get landed with creating a workbook to help us perform some semi automatic audit tasks.

From an auto generated Altiris software report we need to determine if a detected package is:

A: part of a group that we don't want re-deployed (I've created a sheet called CORE and any that don't get deployed we add them to this sheet)

and

B: if it is part of our INSTALL packages it needs to pull up what SCCM package needs applied. (I've created another sheet called SCCM GROUPS and add both the detected package matching the report in one column and the relevant SCCM group in the next column)

So as an example using this formula =IFERROR(VLOOKUP(A3,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

I can query the CORE sheet and if it finds a match it adds a field which i can then use to filter them out leaving only those apps not on the CORE sheet.

With this formula in another column i can then query the SCCM sheet and pull in the SCCM package needed =IFERROR(VLOOKUP(A3,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

So i end up with a front screen that looks something like this...



In the example you can see the fields pulled in to say CORE and the ones pulling in the SCCM group - the top two with #N/A would be apps we need to investigate and decide if its CORE or SCCM.

Works well enough but as users are free to DL and install anything they like on the estate we have multiple entries for slightly different releases of things like Chrome, Firefox, itunes, spotify etc. etc.

We only have one corp SCCM packaged version of these things so ideally i want to group these all under one umbrella so i only need to have one 'Firefox' entry instead of 27 different version entries.

I've tried a few things with wildcards from various other posts/sites but can't get anything to work and not sure if these are specific to Excel 2013 or just i'm doing it wrong?

Happy to supply a sample sheet if needed.

Any help much appreciated.

Not sure if it is easily possible, but I think a sample workbook would be more helpful. Also show the expected results (and why).

View attachment TESTER Software Audit Spreadsheet V2 - Copy.xlsm

Thanks NBVC - Think that's a sample added.

The data in Report sheet, column 'APP' is a sample of the report we would have, at the moment i paste that in (100+ lines or so) and the spreadsheet sorts it out based on the 2 formulas details above.

My life would be a lot easier if i could somehow incorporate 'wildcards' into these lookups (or another method/command if that would be a better way) so that i would only have to enter say 'Adobe Flash Player' in one cell in the SCCM Groups sheet, then add the relevant SCCM package across from it - and it would flaf the same thing for any version of that app - at the moment i need to add every flavor of whatever app i'm dealing with into either the CORE/SCCM sheets so they report corectly, and with a lot of these freeware packages there is inherently loads of slightly different numbered versions of them.

Thanks again anyone who can assist.

Scot

If you add "Adobe Flash Player" to your list in column A of SCCM Groups sheet, then you change the below formula in E3 of the Report sheet, you should get the corresponding value from column B.

=LOOKUP(10^10,SEARCH(" "&'SCCM GROUPS'!$A$1:$A$1435&" "," "&A3&" "),'SCCM GROUPS'!$B$1:$B$1435)

The formula searches for the substrings in your SCCM Groups sheet within the individual cells of Reports sheet to define a match. Does that work for you?

NBVC said:

If you add "Adobe Flash Player" to your list in column A of SCCM Groups sheet, then you change the below formula in E3 of the Report sheet, you should get the corresponding value from column B.

=LOOKUP(10^10,SEARCH(" "&'SCCM GROUPS'!$A$1:$A$1435&" "," "&A3&" "),'SCCM GROUPS'!$B$1:$B$1435)

The formula searches for the substrings in your SCCM Groups sheet within the individual cells of Reports sheet to define a match. Does that work for you?

Click to expand...

Cheers NBVC that works an absolute treat from the test i can do at home, copied it to F3 and edited the relevant bit of the formula so it pulls in the 2nd 'Machine' SCCM group along with the User one - I'll confirm tomorrow from the office on a larger report as soon as i get in. There shouldn't be any limits on repeating the formula down the rows as things grow yeh?

All i'm missing now is a similar formula in Column D 'Status', of the Report Sheet to search the CORE sheet and return either a CORE or ADD THIS result so i can filter the results for printing/export/copying and i'd be a very happy dude.

Thanks for the help NBVC

It would be a similar formula with an If error around it

Eg

=Iferror(Lookup(...),"Add This")

Sorry for the brevity. I am posting from a smartphone.

NBVC said:

It would be a similar formula with an If error around it

Eg

=Iferror(Lookup(...),"Add This")

Sorry for the brevity. I am posting from a smartphone.

Click to expand...

Thanks again NBVC - gave it a try but haven't got the skill set to make it work by simply adding to the code already there :-(

=IFERROR(VLOOKUP(A10,CORE!$A$1:$B$4985,2,FALSE)," - ADD THIS - ")

Last attempt was this

=IFERROR(LOOKUP(10^10,SEARCH(" "&CORE!$A$1:$A$1352&" "," "&A16&" "),CORE!$B$1:$B$1352)," - ADD THIS - ")

The last one looks right as far as I can see. Remember that the text in one of the cells of range CORE!$A$1:$A$1352 needs to be an exact substring of the text in A16.

If you still have issues, please post a sample workbook, but if it worked for the SCCM Group sheet, then it should work here if you are following the same rules.