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

#### jwasten

##### Board Regular
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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)}

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

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.

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

Replies
6
Views
232
Replies
3
Views
290
Replies
1
Views
262
Replies
6
Views
515
Replies
3
Views
276

1,203,600
Messages
6,056,207
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back