Help on INDEX and MATCH function
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!
Best Answer
-
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:
- Click on the Sheet2: MRN Additional Information.
- Select the column you want to reference (e.g., MRN).
- Click on the column header dropdown and choose Reference Another Sheet.
- 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:
- Navigate to Sheet1: Appointments:
- Open Sheet1: Appointments where you want to populate the MRN Language.
- 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).
- Enter the Formula:
- =INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))
- 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.
- 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
I hope this is helpful. If you need more assistance I'm happy to help.
- INDEX Function:
Answers
-
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:
- Click on the Sheet2: MRN Additional Information.
- Select the column you want to reference (e.g., MRN).
- Click on the column header dropdown and choose Reference Another Sheet.
- 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:
- Navigate to Sheet1: Appointments:
- Open Sheet1: Appointments where you want to populate the MRN Language.
- 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).
- Enter the Formula:
- =INDEX({MRN Language}, MATCH([MRN]@row, {MRN}, 0))
- 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.
- 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
I hope this is helpful. If you need more assistance I'm happy to help.
- INDEX Function:
-
Are you able to provide some screenshots for context?
-
@Hunter Taylor This worked perfect! I realize I was creating my reference backwards. Thank you so much for the great detailed response!!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!