Home Pulling multiple lookupvalues in excel's VLOOKUP Formula
Reply: 0

Pulling multiple lookupvalues in excel's VLOOKUP Formula

user2662 Published in May 22, 2018, 9:38 pm

expanding off a question I asked previously, but now I'm a little farther ahead thanks to help from you fine folks and from other sources.

Basically I can pull reports from my company server, they are exported in spreadsheet format where each row is a report, and each column is info about the report (report count, date made, report title, etc), the column I am concerned with has a 4 digit code identifying the group that report came from (A205, A206, B208, Q404, there are thousands) Lets call this column the "Report Number"

I am currently using VLOOKUP to find the code on a reference sheet, and then return the name of the group that code is associated with, so if the code is "A205", the formula will instead return "A-TEAM" in the cell. (I have this paired with a macro which does it for hundreds of rows at once, and fills in name in the next empty column)

Right now this works great...IF there is only one code in the "Report Number" column. My problem arises when a report is completed by multiple groups separated by a comma. So in the "Report Number" column, it might have "A205, A206, B208" and I need the formula to output ALL the decoded names in the same format (I.E. "A Team, B Team, C Team) instead of an error, or just the first one.

So, is there a way to do this with VLOOKUP? without nesting IF functions over and over. Or do I need to modify my Macro?

Here is my current macro that works (when I change the parameters to match my worksheet names and whatnot) you can see where the vlookup formula is entered.

Option Explicit
Sub CustomerCodeLookup()

Dim LastRow As Long
Dim LastColumn As Long
Dim RNColumn As Long
Dim RNFirstCell As String

'identify last row of data
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'get first blank column (by assuming first blank cell in row 1 is the first blank column)  
LastColumn = Cells(1, 1).End(xlToRight).Column + 1
'find the column that has "Report Number"
RNColumn = Range("1:1").Find("ReportNumber", LookIn:=xlValues).Column
'Relative address of first cell in Report Number column for use in the formula
RNFirstCell = Cells(2, RNColumn).Address(False, False)

'Add header to the lookup column
Cells(1, LastColumn) = "Group Name"
'insert formula from row 2 until the last data row
Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)) = "=VLOOKUP(LEFT(" & RNFirstCell & ", 5),'C:\Path\to\pulled workbook\[Codes.xlsm]Codereference'!$A:$O,2,0)"
'Remove formula from cells
Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)) = Range(Cells(2, LastColumn), Cells(LastRow, LastColumn)).Value

End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323073 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO