Need help vlookup and a migraine. Help with vlookup may help with migraine.

Best Answers

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Hey @imtonydillard

    An Index/Match is one approach that will work for you.

    =INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))

    (if you want to hard code in "Aetna MA" , replace the Carrier@row with this. As written, the lookup is dynamic based on what is in the Carrier column, per row, of Sheet1)

    You will have to create the cross sheet references yourself, you cannot simply copy paste the formula.

    When you create the cross sheet references (from the formula window), the name of the ranges will be different than what I have written in the formula above. This is expected as smartsheet generically assigns the names. You can change these - it is a good practice.

    Does this work for your smartsheet? And for the migraine? 😁

    Kelly

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Great! As a point of interest, depending on your sheet, the Index/Match can be a more robust formula and help with sheet performance since you are not carrying an entire 'table' in the formula. Once you need a formula with multiple criteria, the Index/Match easily transitions to the Index/Collect. A vLookup cannot manage multiple criteria. This is why I chose the Index/Match approach.

    I'm glad you got the formula to work for you.

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Hey @imtonydillard

    An Index/Match is one approach that will work for you.

    =INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))

    (if you want to hard code in "Aetna MA" , replace the Carrier@row with this. As written, the lookup is dynamic based on what is in the Carrier column, per row, of Sheet1)

    You will have to create the cross sheet references yourself, you cannot simply copy paste the formula.

    When you create the cross sheet references (from the formula window), the name of the ranges will be different than what I have written in the formula above. This is expected as smartsheet generically assigns the names. You can change these - it is a good practice.

    Does this work for your smartsheet? And for the migraine? 😁

    Kelly

  • @Kelly Moore Mega thanks for your help on this. It worked for my on the first try and because of your guidance I was able to figure out how to write a vlookup formula to do the same.

    =INDEX({sheet 2 Risk column}, MATCH(Carrier@row, {sheet 2 Carrier column},0))

    =VLOOKUP(Carrier@row, {CCIndex Repository Range 1}, 13, false)

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Great! As a point of interest, depending on your sheet, the Index/Match can be a more robust formula and help with sheet performance since you are not carrying an entire 'table' in the formula. Once you need a formula with multiple criteria, the Index/Match easily transitions to the Index/Collect. A vLookup cannot manage multiple criteria. This is why I chose the Index/Match approach.

    I'm glad you got the formula to work for you.

    cheers

    Kelly

  • @Kelly Moore is the 2022's most recommended pain reliever for migraines caused by smartsheets.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!