Help on INDEX and MATCH function

Allie12380

I have tried the INDEX and MATCH function in a number of ways and cannot get it to work. I have read the numerous posts but not getting it.

Sheet 1: Appointments

Field in Sheet 1: MRN

*one MRN can have multiple appointments.

Sheet 2: MRN Additional Information

Fields in Sheet 2: MRN, MRN Language

*MRN is unique ID in this table (no duplicates)

What I am trying to do is have the MRN Language from sheet 2 pull into sheet 1 using MRN@row.

Here are formula versions I have tried:

=INDEX({MRN Language}, MATCH(MRN@row, {MRNInfo1}, 0))

=INDEX({MRN Language}, MATCH(MRN@row, {MRN Master}, 0))

=IFERROR(INDEX({MRN Additional Information}:{MRN},MATCH([Column1]@row,{SS2 Column1},0)),"")

=VLOOKUP(MRN@row, {MRN Additional Details}, 2, 0) *note tried this to see if I could get something simpler to work.

Please teach me how I need to write this formula to work!

Hunter Taylor

Hi Allie12380,

Using INDEX and MATCH Functions in Smartsheet

1. Understanding the Components

• INDEX Function:
  • Purpose: Retrieves a value from a specified range based on a given row and column number.
  • Syntax: INDEX(range, row_number, [column_number])
• MATCH Function:
  • Purpose: Searches for a specified item in a range and returns its relative position.
  • Syntax: MATCH(lookup_value, lookup_range, [match_type])

2. Setting Up Cross-Sheet References

Before writing the formula, ensure that you have established cross-sheet references for the necessary columns in Sheet2: MRN Additional Information.

• Create Named References:
  • {MRN}: Refers to the MRN column in Sheet2.
  • {MRN Language}: Refers to the MRN Language column in Sheet2.

To create a named reference:

1. Click on the Sheet2: MRN Additional Information.
2. Select the column you want to reference (e.g., MRN).
3. Click on the column header dropdown and choose Reference Another Sheet.
4. Follow the prompts to create and name your reference (e.g., {MRN} and {MRN Language}).

3. Writing the Formula

In Sheet1: Appointments, you'll input the formula to fetch the MRN Language based on the MRN.

Formula:

=INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))

Explanation:

• INDEX({MRN Language}, ...):
  • This part specifies the range from which to retrieve the MRN Language. It tells Smartsheet to look into the {MRN Language} column in Sheet2.
• MATCH([MRN]@row, {MRN}, 0):
  • [MRN]@row: Refers to the MRN value in the current row of Sheet1: Appointments.
  • {MRN}: The range of MRNs in Sheet2.
  • 0: Specifies that the MATCH function should look for an exact match.

Step-by-Step Implementation:

1. Navigate to Sheet1: Appointments:
   • Open Sheet1: Appointments where you want to populate the MRN Language.
2. Select the Target Cell:
   • Click on the first cell in the MRN Language column where you want the data to appear (e.g., cell C2).
3. Enter the Formula:
   • =INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))
4. Apply the Formula to Other Rows:
   • After entering the formula in the first cell, use the fill handle (a small square at the bottom-right corner of the cell) to drag the formula down to other rows. This action will automatically adjust the [MRN]@row reference for each row.

I hope this is helpful. If you need more assistance I'm happy to help.

Hunter Taylor

Hi Allie12380,

Using INDEX and MATCH Functions in Smartsheet

1. Understanding the Components

• INDEX Function:
  • Purpose: Retrieves a value from a specified range based on a given row and column number.
  • Syntax: INDEX(range, row_number, [column_number])
• MATCH Function:
  • Purpose: Searches for a specified item in a range and returns its relative position.
  • Syntax: MATCH(lookup_value, lookup_range, [match_type])

2. Setting Up Cross-Sheet References

Before writing the formula, ensure that you have established cross-sheet references for the necessary columns in Sheet2: MRN Additional Information.

• Create Named References:
  • {MRN}: Refers to the MRN column in Sheet2.
  • {MRN Language}: Refers to the MRN Language column in Sheet2.

To create a named reference:

1. Click on the Sheet2: MRN Additional Information.
2. Select the column you want to reference (e.g., MRN).
3. Click on the column header dropdown and choose Reference Another Sheet.
4. Follow the prompts to create and name your reference (e.g., {MRN} and {MRN Language}).

3. Writing the Formula

In Sheet1: Appointments, you'll input the formula to fetch the MRN Language based on the MRN.

Formula:

=INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))

Explanation:

• INDEX({MRN Language}, ...):
  • This part specifies the range from which to retrieve the MRN Language. It tells Smartsheet to look into the {MRN Language} column in Sheet2.
• MATCH([MRN]@row, {MRN}, 0):
  • [MRN]@row: Refers to the MRN value in the current row of Sheet1: Appointments.
  • {MRN}: The range of MRNs in Sheet2.
  • 0: Specifies that the MATCH function should look for an exact match.

Step-by-Step Implementation:

1. Navigate to Sheet1: Appointments:
   • Open Sheet1: Appointments where you want to populate the MRN Language.
2. Select the Target Cell:
   • Click on the first cell in the MRN Language column where you want the data to appear (e.g., cell C2).
3. Enter the Formula:
   • =INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))
4. Apply the Formula to Other Rows:
   • After entering the formula in the first cell, use the fill handle (a small square at the bottom-right corner of the cell) to drag the formula down to other rows. This action will automatically adjust the [MRN]@row reference for each row.

I hope this is helpful. If you need more assistance I'm happy to help.

Paul Newcome

Are you able to provide some screenshots for context?

Allie12380

@Hunter Taylor This worked perfect! I realize I was creating my reference backwards. Thank you so much for the great detailed response!!!!!