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

@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

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))
Help Article Resources
Categories
Check out the Formula Handbook template!