Finding matching info in dif columns & rows - maybe the impo

[jwasten]

I have a spreadsheet with approx 100 rows that has data in Columns A-O. Columns A-I display info copied from one sheet and Columns L-O display info copied from a different one. I'm hoping to merge a bit of the info.

If A1 and B1 matches any 2 adjacent cells in Columns L & M (no matter what row), then I want to put the data in Column O in that matching row into G1. An example might be -
A1 contains 08-0301-703
B1 contains 00
That same info is found in L9 (08-0301-703) and M9 (00) so I want to copy the data found in O9 (11) and paste it into G1.

Is there a formula to do such a thing or have I asked for the impossible?!

Thanks.

Janet

 

[Cam]

In cell G1 use this formula and fill it down it should meet your conditions. If you do not wish for it to say false when the conditions aren't met use "" instead.

Enter Control+Shift+Enter rather than enter, excel will put the { around the formula

{=IF(A1:B1=L1:M1,O1,FALSE)}

 

[jwasten]

I tried this but it found only 1 of 5 matches. A5 & B5 matched the data in L6 & M6 but it copied the data in O6 (11.5) and put it into G6 rather than G5.

I double checked the formula and it appears to match your example. Any other ideas?

Janet

 

[Aladin Akyurek]

On 2002-08-30 07:05, jwasten wrote:
I have a spreadsheet with approx 100 rows that has data in Columns A-O. Columns A-I display info copied from one sheet and Columns L-O display info copied from a different one. I'm hoping to merge a bit of the info.

If A1 and B1 matches any 2 adjacent cells in Columns L & M (no matter what row), then I want to put the data in Column O in that matching row into G1. An example might be -
A1 contains 08-0301-703
B1 contains 00
That same info is found in L9 (08-0301-703) and M9 (00) so I want to copy the data found in O9 (11) and paste it into G1.

Is there a formula to do such a thing or have I asked for the impossible?!

Thanks.

Janet

In G1 array-enter:

=INDEX($O$1:$O$18,MATCH(A1,IF($M$1:$M$18=B1,$L$1:$L$18),0))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Adjust the ranges to suit. Note that you are not allowed to use in array-formulas whole columns like O:O as references.

 

[jwasten]

Absolutely Awesome!!! I really thought I was asking for the impossible this time. I should never underestimate the abilities of the users of this forum.

Thanks again!!

Janet