VLOOKUP with multiple criteria

Options

I have a VLOOKUP formula referencing another sheet and it works great. The problem I run into is when I need the same formula to look up multiple criteria before returning a value.

Current formula (works): =VLOOKUP(Date@row, {BYP RSS Links}, 7, false)

I want everything in the formula to remain the same, except I want it to reference the Date column and the Description column before returning the value in column 7 of the other sheet. Currently that returns that data only based on the Date column. I have tried to build it using the & symbol like you would in Excel, but it doesn't seem to like that. Any suggestions?

Tags:

Best Answer

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    When I have been faced with this problem I create a helper column in both sheets, and concatenate the two fields. In your case, I would call the field DATE DESC and it would be something like DATE@row+DESCRIPTION@row.

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    When I have been faced with this problem I create a helper column in both sheets, and concatenate the two fields. In your case, I would call the field DATE DESC and it would be something like DATE@row+DESCRIPTION@row.

  • Megan Yaussi
    Megan Yaussi ✭✭✭✭
    Options

    I had thought about that and hoped there was a simple formula solution, but will likely go with that. Thank goodness columns can be hidden. :) Thank you for the help!

  • Lisa Vercellone
    Lisa Vercellone ✭✭✭✭
    edited 07/26/22
    Options

    I ran into this issue as well and I couldn't add a helper column as the sheet I was referencing wasn't mine. What worked for me was to write a nested IF statement. First it looks to see if the first name (Range 1) is on the referenced sheet. If it isn't, it puts "#No Match". If it is there, it does a second VLOOKUP on the last name (Range 2) and returns the information.


    IF(VLOOKUP([First Name]@row, {Provider Information Range 1}, 1, false) = [First Name]@row, VLOOKUP([Last Name]@row, {Provider Information Range 2}, 2, false))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!