MATCH a range with a column

[bittubadri]

My 2 input ranges are:
C3:Q6 & T10:T14 generating positive values. These ranges are non-contiguous (as shown).

In my output range, C8:Q8, I want to get 10 where the corresponding column of C3:Q6 matches EXACTLY with T10:T14.


Formula required for P10 & P11
How to accomplish? Thanks in advance.

[alannavavaldez]

Sorry, Iīm new to the forum... I still donīt know how to attach an excel file.

In cell C10 copy and paste this formula. I think this should work.

=IF(SUM((SORT(C3:C7)=SORT($T$10:$T$14))*1)=5,10,0)

[Phuocam]

Maybe like this?

=IF(AGGREGATE(15,6,COUNTIF($T$10:$T$14,C3:C6),1)=0,0,10)

[wk9128]

Cell C8 formula , Drag right

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[bittubadri]

Your formula works. Thanks

[bittubadri]

Both formula did not worked!!

[wk9128]

@bittubadri You're Welcome. Glad to help . Thank You for the feedback

[bittubadri]

Some modification in the formula is needed.
I inserted 1 row between 2nd & 3rd row. Formula returns wrong answer (0). It should be 10. Please see the attached file.
Modified formula needed.

[JeteMc]

Try the following array entered formula** in cell I9:

Please Login or Register  to view this content.

**Array entered formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[bittubadri]

When formula is entered in C9, the answer given is 10 (wrong). It should be 0 since C3:C7 does not match with T11:T15.
However, I9=10 (correct) since I3:I7 matches with T11:T15
Some rectification in the formula needed.

[JeteMc]

Paste the following into cell C9 and drag the fill handle over to cell Q9:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bittubadri]

Thanks JeteMc for solving.....you made it very simple.....I was going other way for the solution.... Thanks I learnt something from you.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.