Index Match - Returning a blank Value

Lbrady
Lbrady ✭✭
edited 03/11/24 in Formulas and Functions

Hi All,

I am working on using an Index/Match to pull physician email addresses based on their EMP CID, which is stored on a separate physician roster sheet.

image.png

=Index({Physician Roster 2024 Email}, MATCH([EMP CID]@row, {Physician Roster 2024 EMP CID},0))

When adding a physician to this sheet, if I leave the EMP CID field blank, it automatically pulls over the email address of the first physician listed on the roster who also has a blank EMP CID field.

image.png

Is there a way to write the formula so that only exact matches are pulled and blanks excluded?

· Share on FacebookShare on Twitter

Best Answer

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    Answer ✓

    Hi Lbrady,

    Something like this perhaps?

    =IF(EMP CID]@row="","",Index({Physician Roster 2024 Email}, MATCH([EMP CID]@row, {Physician Roster 2024 EMP CID},0)))

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!