Forum Discussion

TSimsD's avatar
TSimsD
Copper Contributor
Jan 03, 2023

vlookup error with multiple criteria

Hi,

 

I have a spreadsheet with a list of training courses individuals have completed and the date, which is a connected export of a sharepoint list. I have another spreadsheet with is the training matrix for the company and i wanted to automatically update the matrix with the dates from the sharepoint list.

I found a solution which works brilliantly for all but two of the courses!

I combine the names and title of the course in the export sheet:

I then use the vlookup in the matrix sheet to look up the combined names with the title of the training which is the header of the column:

 

=VLOOKUP([@Combinedname]&Table32[[#Headers],[Safety Induction]],'Exported List from Training Register.xlsx'!Table_query__32[[#All],[Combinedname]:[Date completed]],8,FALSE)

 

I know this formula works as it works for the 10 other courses in the matrix. I have tried copying the names and title from the source sheet so i know that they are exact but it still cannot find the reference.

As you can see I have even tried with 1 1 as a name.

If anyone has any ideas I would love to hear them.

 

cheers

Tony

  • TSimsD 

    It will return error or #N/A to your formula, because your lookup value and doesn't match on your reference table.
    Just change your "combinedname" on your export sheet without including the title of training.
    Formula: [@[First Name]&@[Surname]]

  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

    TSimsD 

    It will return error or #N/A to your formula, because your lookup value and doesn't match on your reference table.
    Just change your "combinedname" on your export sheet without including the title of training.
    Formula: [@[First Name]&@[Surname]]

    • TSimsD's avatar
      TSimsD
      Copper Contributor

      Rodrigo_ 

      Thanks rrstrivera,

      the formula is correct. As I said it works on other courses. The lookup is the combined first name & surname coupled with the title of the training.

       

       

       

      Formula: =IFERROR(VLOOKUP([@Combinedname]&Table32[[#Headers],[Manual Handling]],'Exported List from Training Register.xlsx'!Table_query__32[[#All],[Combinedname]:[Date completed]],8,FALSE),"")

       

      Cheers

      Tony

       

      • Rodrigo_'s avatar
        Rodrigo_
        Steel Contributor


        Hi, TSimsD 

        Since we solved it on thru PM, there's a link to mark the solution at the bottom.
        This helps for those who search.

         

        Thanks!

Resources