Google-sheets – Google Sheets – search multiple columns for specific text

google sheets

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

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

Best Answer

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.