Cross-Sheet Index Match Reference

LaurenSteds

I am trying to pull a budget number from one sheet into another based on the ROWID which is pasted into sheet 2 by the user. I need the formula to look at a Quarter column in sheet 2 and determine based on the content, bring back the number in the Q1, Q2, Q3 or Q4 column in sheet 1.

This is the formula AI provided but it's returning #Invalid Value. If I update to -1 matching because the data is not in numerical order, I get #NoMatch.

=IF([Quarter]@row = "Q1", INDEX({Q1 Range}, MATCH([ROW ID]@row, {Q1 Range}, 0), 25), IF([Quarter]@row = "Q2", INDEX({Q1 Range}, MATCH([ROW ID]@row, {Q1 Range}, 0), 30), IF([Quarter]@row = "Q3", INDEX({Q1 Range}, MATCH([ROW ID]@row, {Q1 Range}, 0), 35), IF([Quarter]@row = "Q4", INDEX({Q1 Range}, MATCH([ROW ID]@row, {Q1 Range}, 0), 40), ""))))

I have also tried VLOOKUP + IF function. It works for a singular IF statement but not for multiple.

=IF(Quarter@row = "Q1", (VLOOKUP([ROW ID]@row, {Q1 Range}, 25, false)), "")

Paul Newcome

Are you able to provide a screenshot for context?

LaurenSteds

^ Sheet 2 where I am putting formula based on ROW ID pasted in

^ Sheet 1 with source data

LaurenSteds

As an update, I switched to a Vlookup function, even though I've definitely tried that and got it to work! Thanks @Paul Newcome for jumping in quickly!

Paul Newcome

Glad you were able to get something working.

I do usually suggest an INDEX/MATCH instead though because VLOOKUPS provide zero flexibility and reference A LOT of data that doesn't need to be referenced (pushing you closer to a limit than you really need to be).

This is how I would structure an INDEX/MATCH to work:

=INDEX(IF(Quarter@row = "Q1", {Q1 Column Only}, IF(Quarter@row = "Q2", {Q2 Column Only}, IF(Quarter@row = "Q3", {Q3 Column only}, {Q4 Column Only}))), MATCH(Quarter@row, {Quarter Column only}, 0))