Comparing multiple columns in multiple sheets

**Quijibo187** · 04-13-2009, 12:38 PM

Hi there, new to the forums, and I'm stuck (why else would I be here?)
What I'm trying to do is develop a formula to:
- look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2.
then, display an X

Thanks

p.s. I tried the search tool, but was unsuccessful.

**NBVC** · 04-13-2009, 12:47 PM

Try something like this:

=IF(ISNUMBER(MATCH(A1,'SHEET2'!A:A,0)),"X","") copied down the column

where A1 contains first item in Sheet1 to match up against column A in Sheet2...

adjust ranges and sheetname to suit.

**Quijibo187** · 04-13-2009, 12:51 PM

will that work if the columns are not the same length?
One sheet has a list of coustomers that are an credit hold,
another sheet is a list of purchase orders, the same customer will appear multiple times on the purchase order sheet (which has ~800 rows) wheras the customer sheet has less than 200 rows.

I'm starting to think this might be easier in Access.

**NBVC** · 04-13-2009, 12:56 PM

That is a formula that you would put in the cell adjacent to the first item you want to find...and then you copy the formula down so it checks each cell in the first sheet against the entire column A:A in Sheet2!

Once you copied it down the column in Sheet1! you should see "X's" adjacent to cells that have matches in the other sheet.

You can also go vice-versa with similar formula in Sheet2! comparing it to entire column in Sheet1!

**Quijibo187** · 04-13-2009, 01:06 PM

beauty, thanks, I'll give it a shot.

**NBVC** · 04-13-2009, 02:04 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Quijibo187** · 04-13-2009, 02:27 PM

was just about to do that, the formula worked, and with a bit of manipulation, I was able to make it suit exactly what I needed.

Thanks again, I'll be back I'm sure!