0

How Can I compare the entries on TAB: SUMMARY SLS, column B with SUMMARY P&S, column B and bring back a "X" if a match is found in the cell with the formula? SUMMARY SLS and SUMMARY P&S are database tab/sheet names within the same spreadsheet.

The cells in column B are filled with unique values on each separate sheet but should match between the two sheets. If one of the values on one sheet doesn't have a match on the other sheet then I would like it to return a blank. My formula will be typed in column G.

The only thing I have tried so far is a simple vlookup which would bring the corresponding match from the other spreadsheet but what I need is to have my formular answer the question, "Is there a match between SUMMARY SLS Column B and SUMMARY P&S column B and if so place an "X" in this cell, if not leave blank

Improve this question
4
  • This is not a script/code writing service, but we are willing to help you where you have gotten stuck. Please tell us what you have researched and attempted to resolve this. It may be helpful to read How to Ask to improve your question.
    – CharlieRB
    Commented Jun 24, 2015 at 18:14
  • The only thing I have tried so far is a simple vlookup which would bring the corresponding match from the other spreadsheet but what I need is to have my formular answer the question, "Is there a match between SUMMARY SLS Column B and SUMMARY P&S column B and if so place an "X" in this cell, if not leave blank.
    – Rebecca Rickey
    Commented Jun 24, 2015 at 18:30
  • I added those details to your question. Can you please give the formula you are using. Sounds like you are close to a solution.
    – CharlieRB
    Commented Jun 24, 2015 at 19:02
  • =if(B:B='SUMMARY P&S'!B:B,"X","")
    – Rebecca Rickey
    Commented Jun 24, 2015 at 19:57

2 Answers 2

Reset to default
1

Based on what I have understood...You can explore VLOOKUP function. I am assuming that "X" is content of column somewhere next to the column B in P&S sheet.

Sample formula

=IF(ISERROR(VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE)),"",VLOOKUP(B1,'P&S'!$B$1:$C$6,2,FALSE))

B1 - Contents of cell B1 (in SLS sheet) VLOOKUP B1 against range of B1 thru B6 (in this case) in sheet P&S and if match is found pull contents of 2nd column (your X) in formula cell.

IF is required to remove #N/A (not found) and replace with "" (Blank)

Don't forget prefix $ to prevent range being moved down as you copy formula

Improve this answer
5
  • Your assumption is wrong. The OP wants to return the text "X" if a match is found.
    – CharlieRB
    Commented Jun 24, 2015 at 19:01
  • There is no column with (my X). I would like the formula to add a "X" if it finds a match but there is not an existing column in sheet P&S next to the column it's looking at to compare with.
    – Rebecca Rickey
    Commented Jun 24, 2015 at 19:17
  • 1
    In that case the sample formula is =IF(ISERROR(VLOOKUP(B1,'P&S'!$B$1:$B$6,1,FALSE)),"","X")
    – patkim
    Commented Jun 24, 2015 at 19:21
  • I would like for the formula to Insert a "X" if it finds a match on the second sheet into the cell
    – Rebecca Rickey
    Commented Jun 24, 2015 at 20:30
  • Got iT! Thank you :-) Your last formula works
    – Rebecca Rickey
    Commented Jun 24, 2015 at 20:34
1

When you are comparing the columns row by row you can get rid of VLOOKUP

Put the following formula in G1 cell:

 =IF('SUMMARY SLS'!$B1='SUMMARY P&S'!$B1,"X","")

then copy G1 cell and paste (I'd reccommend paste-special/formula) on G columns' rows at least for the number of B columns' rows you need to compare.

EDIT:

In the case you are comparing every single row value in column with all values in another one then the formula in G1 should be as follows:

=IF(IFERROR(VLOOKUP('SUMMARY SLS'!$B1='SUMMARY P&S'!B:B,1,FALSE),0)>0,"X","")

or even a bit more concise:

=IF(ISERROR(VLOOKUP('SUMMARY SLS'!$B1,'SUMMARY P&S'!B:B,1,FALSE)),"","X")
Improve this answer
6
  • My formula cell is actually located on the SUMMARY SLS tab so then I would skip the Tab reference in the first part of the formula, right? Will this formula compare each cell against the entire column on the other sheet to see if there is a match?
    – Rebecca Rickey
    Commented Jun 24, 2015 at 19:36
  • Yes, correct, in case the G column is located in one of the two SUMMARY *** sheet you can do without referencing it on your formula (so, in this case, you can remove 'SUMMARY SLS'.). And yes, if you paste the formula all along G column rows you should notice that the initial $B1 reference will be incrementing in each row in a way that G2 cell will compare B2 cells and so on. (So, if you have to match 100 rows paste the formula until G100 and see the last comparison will be on B100.)
    – danicotra
    Commented Jun 24, 2015 at 19:53
  • like this? =if(B:B='SUMMARY P&S'!B:B,"X","")
    – Rebecca Rickey
    Commented Jun 24, 2015 at 19:56
  • I'm getting a #Value! Error
    – Rebecca Rickey
    Commented Jun 24, 2015 at 20:02
  • Selecting to compare 1 cell in column B on first sheet to entire column B on second sheet in order to find the match that might be somewhere in that column but not necessarily in the exact same cell reference as on the other tab, but I am getting a #Value Error.
    – Rebecca Rickey
    Commented Jun 24, 2015 at 20:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .