0
202
ViewsHow to use IFS more concisely- excel
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.
2 answers
Answer question0
You may want to use FILTER to dynamically show a range based on matching criteria
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
0
you can use either function
Normal function =IF(B2=1,"Found the number 1","")
Range function =IF(B2:B27=1,"Found the number 1","")
