Formula to return transposed values based on set of criterias

[Ramzes]

Hello Valued Experts!

As you have helped me in the past and guided through dark patches of Formulas I am hoping you will be of assistance to me once again.

I am working on a plan overview and need to add formula into Sheet2 starting in E2 that will bring back the values from Sheet1 column G based on the following conditions:

It needs to search for the W/O in column B and for the ops order in column N to copy the value from column G in Sheet1 and return it to the relevant field in the Sheet2.

Sheet 2 W/O are in column A and the ops order are in row 1.

I know of sumifs and countifs but nothing to bring the actual text back.

I have attached a file as an example.

Appreciate your help.

Thank you in advance,

Simon.

[63falcondude]

Try this in E2:

=IFERROR(INDEX(Sheet1!$G$2:$G$148,INDEX(MATCH($A2&E$1,Sheet1!$B$2:$B$148&Sheet1!$N$2:$N$148,0),0)),"")

[Ramzes]

Hello 63falcondude,

I have tried your suggestion and it works magic!

Thank you very much. I will analyse it for whatever many times before I completely understand its mechanics, but I wouldn't have thought of adding index in within index and using & to add criteria in match function. It is good to know that this can be done.

Thank you.

[63falcondude]

Great, happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.