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

Kathleen Livelli

Hi. I've found plenty of examples of how to use INDEX and MATCH in place of VLOOKUP in SmartSheet, but no matter how many help topics and forum Q&As I've searched, I cannot find a clear example of how to set up INDEX and MATCH for an HLOOKUP equivalent in Smartsheet. Can someone please help?

Genevieve P.

Hi @Kathleen Livelli

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

Lynn B

@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.

Genevieve P.

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

Lynn B

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.

Genevieve P.

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 hard-coding 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))