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

  • 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 Jun 24 '15 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 Jun 24 '15 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 Jun 24 '15 at 19:02
  • =if(B:B='SUMMARY P&S'!B:B,"X","") – Rebecca Rickey Jun 24 '15 at 19:57
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|||||
  • Your assumption is wrong. The OP wants to return the text "X" if a match is found. – CharlieRB Jun 24 '15 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 Jun 24 '15 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 Jun 24 '15 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 Jun 24 '15 at 20:30
  • Got iT! Thank you :-) Your last formula works – Rebecca Rickey Jun 24 '15 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|||||
  • 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 Jun 24 '15 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 Jun 24 '15 at 19:53
  • like this? =if(B:B='SUMMARY P&S'!B:B,"X","") – Rebecca Rickey Jun 24 '15 at 19:56
  • I'm getting a #Value! Error – Rebecca Rickey Jun 24 '15 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 Jun 24 '15 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.