Use returned value from one index match inside another index match formula

Matt Kirby

I have searched and have not found someone with this same question, but I'm sure it's probably there, but stated differently.

Index/Match 1:

Unique ID returns "X" value from Sheet 1 and populates cell in Sheet 2

=INDEX({Sheet1 reference value to return}, MATCH(X@row, {Range from Sheet1}, 0))

This one works with no issues, the second one is where I am having a problem...

Index/Match 2:

From Sheet 2 Use "returned value from index/match 1" to lookup "Y" value in another sheet (Sheet 3).

=INDEX({Sheet3 reference value to return}, match (returned value from index/match sheet1), {range on sheet3}, 0) )

This comes back as "Unparseable"

Might be something simple or impossible, I'm open to education and suggestions about this.

Sorry if my explanation is clunky or inaccurate.

Thank you!

Austin Smith

@Matt Kirby

Nothing looks inherently wrong with your formatting except that you have one extra closing parenthesis in your second match function (may just be a typo).

Unparseable more often than not means the formula's trying to formulate with data that doesn't compute. Is it possible that the first index is returning an unusable answer? (embarrassingly easy to overlook... not that I'd know anything about that...😐️)

Do you have screenshots for context of data formats, etc?

Matt Kirby

Thank you, @Austin Smith After further testing, it seems this problem is more than just my index match formulas. I am trying a IF or an IF(ISBLANK) on a returned value in a cell from an index/match and that is not working either. Is what I am trying to do supported? Does anyone have an example of a working returned value from an index/match formula that they are using inside of another formula?

Matt Kirby

Looks like I am using the wrong formula for this. I should be using nested formulas from what I have been able to determine.

Austin Smith

@Matt Kirby

I use a variety of different nested formulae, some simple, some extremely not simple. You can definitely do nested index functions, and I'm not sure what you have is simple enough to be solved by nested ifs.

This test formula just worked on a test sheet I had from another community issue:

=INDEX([Column4]11:[sum 4 and 6]13, MATCH(INDEX([sum 2 and 4]1:[Column4]10, MATCH(Test2, [sum 2 and 4]1:[sum 2 and 4]10), 2), [Column4]11:[Column4]13, 0), 2)

None of that (most likely) fixes unparseable issues. If IF( formulae aren't working, you've likely got a data format problem. What happens when you slap an IFERROR( in there?

What you're doing is definitely supported, we just need to do some digging on root cause.

Screenshots would help with context, if you can.