Info added after as response to first comment: I just realized that the IF(PAGE1!C5="OPTION1" probably wont work, probably because I haven't described the wish good enough.

I will describe with this screenshot: On PAGE1 C5 is set to OPTION1, and there are selected 2 parts (DEL 1 and DEL 2). Because of this, I would like the value from A5 (must be same line as OPTION1) to be copied to PAGE2 G5 and G6 (to show the 555 next DEL 1 and DEL 2 on PAGE2).

So, the code needs to be able to work in multiple cells on PAGE2 (next to each "NAME", and search all of COLUMN D to H on PAGE1, and return the value if it finds a match in a row where STATUS=OPTION1, and one of the PART selected matches that next to the cell with the code on PAGE2.

Apologies if this doesn't make sense, I have the end result in my head, but no the way there :-)

New screenshot to clarify description of question


Would like some help to have this code updated, that I was helped to on this forum. Here is the file: https://docs.google.com/spreadsheets/d/1yOapcmzWaaNd50Z86Wpszfu5tKJXhlI2SGm-51kA6Hg/edit?usp=sharing

The code which is currently used is: =IF((IF(PAGE1!C5="OPTION1",1,0))+(IFERROR(MATCH(H5,PAGE1!D5:H5,0),0))>1,PAGE1!A5,"")

But instead of it only looking in PAGE1!D5:H5 I would like for it to look in PAGE1!D:H (the entire columns).

Point is, when it finds a match in any column/row, it will copy a number from the row with a match to this cell.

On PAGE1!E7 "DEL 2" is selected, but 333 (PAGE1!A7) isnt copied to PAGE2!G8 - which is the goal. But only because PAGE1!C7 is OPTION1 - if change to OPTION2 it should remove the value in PAGE2!G8 again.

1 Answer 1


Try =if((IF(PAGE1!C5="OPTION1",1,0))+(if(iserror(lookup(H5,PAGE1!$D$5:$H$6)),0,1))>1,PAGE1!A5,"")

This uses lookup for the search, and this function works across a multidimensional range or array.

I have a feeling that there is probably an answer lurking based on QUERY or FILTER, but that's not my strength.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.