Need a Clear Example of Using INDEX and MATCH in lieu of HLOOKUP
Answers

It does sound like you could use an INDEX(MATCH formula to replicate what you want to do, however it would be helpful to know a bit more about your process. Would you be able to share a screen capture of the source sheet with an explanation of what data you want pulled across?
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value you’re looking to match}, 0))
Or, an Index Match with multiple criteria is INDEX(COLLECT. See this other Community post here for an example.
Let me know if you still have questions!
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

@Genevieve P. , that formula you included is still referencing columns. What I think Kathleen (and I now) are looking for is where it references rows like a HLOOKUP.
=INDEX({row with value to return}, MATCH([value to match]@row, {Row with value you're looking to match},0)).
=INDEX({SP Initial  Total Cost}, MATCH(reference@row, {SP Initial  Months}, 0))
This formula in my example is returning #INVALID COLUMN VALUE however I have confirmed all columns and cells referenced are text/number format. The cross table references are ranges of 1 row each across 31 columns.
Any help would be appreciated.

Hi @Lynn B
Would you be able to provide a screen capture of the sheet you're looking in to?
You could use an INDEX(MATCH(MATCH to find the correct column, and the correct row to bring back a specific cell:
=INDEX({range across columns/rows}, MATCH([Row Value]@row, {Column with unique row values}), MATCH("Column Name", {helper Row in sheet with Column Name}))
See: INDEX Function
In your example, I'm not sure how the two {references} relate to one another. Are they two completely separate rows? What is it you're trying to bring back, and how are you defining this?
Thanks!
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Hi Genevieve,
I managed a workaround by hardcoding which row I want the data from using this formula:
=INDEX({SP Initial  Total Cost}, 61, MATCH(reference@row, {SP Initial  Months}, 0))
Where reference@row is a text value
SP Initial  Total Cost is a range of data across many columns and many rows starting at row 2 and ending at row 62
SP Initial  Months is the text values in row 2.
So right now the formula is searching the entire range (SP  Initial Cost) for the value in row 2 that matches the value in the row in reference column and returning the value in the 61st row. If anyone adds rows, this formula will break like any other VLOOKUP or HLOOKUP formula.

Hi @Lynn B
Thank you for sharing your solution! If you want newly added rows to be included in the range, you could select the entire columns as the range in {SP Initial  Total Cost}.
Then instead of hardcoding the row number in the formula, you could have that row number be in a cell in the sheet where the formula exists. That way you can easily update the number without needing to change the formula:
=INDEX({SP Initial  Total Cost}, [Row Number]@row, MATCH(reference@row, {SP Initial  Months}, 0))
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!