How to pull information from a cell of different sheet when the given conditions are met?

ABHI

I tried using If & Contains but i realized it is doing for the columns but I required my formula to look at rows not the columns. And if given conditions are met, I want to pull the information from that row. Please help?

David Joyeuse

Hi @ABHI

The formula in your [Work Order No.] column on the first sheet should be this one:

=INDEX(COLLECT({Work Order No Range}, {Equipment Range}, [Machine/Equipment]@row, {PM SCHEDULED Range}, "YES"),1)

That is if I understood your screenshot correctly.

Hope it helped!

Mike Wilday

Have you considered using a vlookup? or an INDEX/Match collaboration. The vlookup will match data from one sheet with data from another on a column, and then look across the row to return a specific value based on the tie. Hopefully one of these articles will point you in the right direction.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Feel free to share an example or a screenshot with protected or dummy data. That will help the community recommend a specific solution.

ABHI

Thank you for the help Mike.

But I am trying to get values from another sheet and from the same rows not from column. How can we write the formula?

Can you give me some examples?

Mike Wilday

Can you share examples of data from both sheets? Maybe dummy data that represents the original data?

ABHI

Here, I wanted to check if the equipment match with other Sheet and check if PM scheduled has "YES" if that conditions is fulfilled then I want to pull WORK ORDER No. from the second sheet to the first sheet.

David Joyeuse

Hi @ABHI

The formula in your [Work Order No.] column on the first sheet should be this one:

=INDEX(COLLECT({Work Order No Range}, {Equipment Range}, [Machine/Equipment]@row, {PM SCHEDULED Range}, "YES"),1)

That is if I understood your screenshot correctly.

Hope it helped!

ABHI

Hi @David Joyeuse

Thank you. That worked