Using Index Match to Reference 2 separate sheets

dharberts

Trying to write a formula that uses one distinct number and references multiple sheets to bring back information

Paul Newcome

In that case you would use somethign along the lines of...

=IFERROR(INDEX({Sheet 1 Pull Range}, MATCH([Match Column]@row, {Sheet 1 Match Range}, 0)), INDEX({Sheet 2 Pull Range}, MATCH([Match Column]@row, {Sheet 2 Match Range}, 0)))

If you already have the INDEX/MATCH portion figured out, then here is the basic idea to expand the syntax to additional sheets.

sheet_1_index

IFERROR(above_statement, sheet_2_index)

IFERROR(above_statement, sheet_3_index)

IFERROR(above_statement, sheet_4_index)

sheet_1_index

IFERROR(sheet_1_index, sheet_2_index)

IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index)

IFERROR(IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index), sheet_4_index)

Basically you just keep wrapping in IFERROR functions and entering the next sheet's INDEX/MATCH.

Paul Newcome

Are you able to provide more details?

Are you trying to locate it on one sheet then move to another sheet if it isn't on the first?

dharberts

yes

Paul Newcome

In that case you would use somethign along the lines of...

=IFERROR(INDEX({Sheet 1 Pull Range}, MATCH([Match Column]@row, {Sheet 1 Match Range}, 0)), INDEX({Sheet 2 Pull Range}, MATCH([Match Column]@row, {Sheet 2 Match Range}, 0)))

If you already have the INDEX/MATCH portion figured out, then here is the basic idea to expand the syntax to additional sheets.

sheet_1_index

IFERROR(above_statement, sheet_2_index)

IFERROR(above_statement, sheet_3_index)

IFERROR(above_statement, sheet_4_index)

sheet_1_index

IFERROR(sheet_1_index, sheet_2_index)

IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index)

IFERROR(IFERROR(IFERROR(sheet_1_index, sheet_2_index), sheet_3_index), sheet_4_index)

Basically you just keep wrapping in IFERROR functions and entering the next sheet's INDEX/MATCH.

dharberts

OK here is what I did for the Formula

=IFERROR({ecoaid}, MATCH([Return Order #]@row, {Order#1},0)), INDEX({RID}, MATCH([Return Order #]@row,{RO#},0)))

Received #UNPARSEABLE

dharberts

LOL I found my MISTAKE!!! LOL thank you

Paul Newcome

Glad you got it sorted.