• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple vlook up or index match formula

R

rajkenya1

Member
Hi all


I am trying to create a formula in cell C2 in the “workings” tab which basically matches information contained in cell b2 and a2 to the information contained in the “staff list” tab and then return the value from column C from the “staff lists’ tab.


I am unsure whether this needs a vlook formula (with nested if and statements) or can be done with an index match formula.


Please help


See attached spreadsheet
 

Attachments

Hi Raj,

One doubt:

Say for
Assistant Coordinator - CDC ---- Full Time there are two column C value. Which value you want?

Regards,
 
Hi Somendra

Thank you as always for your prompt response.

Basically if you look at the staff list sheet, cell A2 and A3 have both got Assistant Coordinator - CDC and then if you look at B2 and B3 they are either part time or full time.

I note your doubt, therefore we may have to use cell D2 as well in the forumla. Therefore it is comparing 3 columns (a b and d rather than just a and b) and then returning the result. I hope this makes sense now.

Thanks again for your help




Somendra Misra said:
Hi Raj,

One doubt:

Say for
Assistant Coordinator - CDC ---- Full Time there are two column C value. Which value you want?

Regards,
Click to expand...
 
Hi Azumi

Thanking you for your reply. The answer is great but just wondering whether you can use 3 columns such as (a b and d rather than just a and b) as that way the result will be on one column only and not multiple column. Is there a way we can do this my friend.

Thanks

azumi said:
Perhaps something like this?
Click to expand...
 
Its difficult for excel to return multiple occurences in single column, maybe UDF can do that, maybe other member has solution for you with macro or UDF...
 
Thank you Azumi

Your answer has been very helpful. I will work using your formula.

Thanks again.

Kind regards

Raj

azumi said:
Its difficult for excel to return multiple occurences in single column, maybe UDF can do that, maybe other member has solution for you with macro or UDF...
Click to expand...
 
Hi rajenkya1,

When you refer to column D, you mean you want to add it to you lookup criteria?? like below

Assistant Coordinator - CDC Full Time DWAC51 MCS CDCS CA A/COORD 5.1
Assistant Coordinator - CDC Part Time DWAC51 MCS CDCS CA A/COORD 5.1
Assistant Coordinator - CDC Full Time DWAC53 MCS CDCS CA A/COORD 5.3
Assistant Coordinator - CDC Part Time DWAC53 MCS CDCS CA A/COORD 5.3

..and then lookup?
 
Hi Faseeh

Thanks for your reply. I have had a look at the file and i think it would work but i get this in #Name? in column E. What does UDF mean?

Faseeh said:
Please see attached file, i have added a helper column a LookupConcat() UDF from Rick Rosenthen's webpage, that will do the task, please see and comment.
Click to expand...
 
Hi Raj,

The file sent by @Faseeh uses a UDF (User Defined Function) built on VBA and is not a standard Excel function. If you try to do it in your original file the #Name error will come until unless you copy the code through VBA editor to your file. Below is the code:

Code: 
Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                      Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
                 
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
 
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
    (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
   
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
 
End Function

Just copy this code in a new module of VBA editor and it will start working.

If you have any doubt regarding copying this code to VBA editor, just write back.

Regards,
 
Thanking you all for your replies.

I am not that great with VBA but will try my best to work on it.

Thanks and best regards

Raj
 
You must log in or register to reply here.
Back
Top