I'm trying to search many cells in the B column for the number 1. When the 1 is found, I want to display the value in the A column of the row in which the 1 was found. I am very new to excel formulas, but this is what I tried:

=IFS('8&Under'!B3=1,'8&Under'!A3,'8&Under'!B4=1,'8&Under'!A4,'8&Under'!B5=1,'8&Under'!A5,'8&Under'!B6=1,'8&Under'!A6,'8&Under'!B7=1,'8&Under'!A7,'8&Under'!B8=1,'8&Under'!A8,'8&Under'!B9=1,'8&Under'!A9,'8&Under'!B10=1,'8&Under'!A10,'8&Under'!B11=1,'8&Under'!A11)

It works, but is horribly long, and I will need to recreate it many times for the whole application to work out.

Thanks.

CodePudding user response：

You may want to use FILTER to dynamically show a range based on matching criteria