Match or if return value problem

Invexskm

New Member
Joined
Feb 3, 2010
Messages
4
HAVE 6 COLUMNS OF DATA, WANT TO MATCH COLUMNS 1 AND 4, 2, AND 5 AND 3 AND 6. WOULD LIKE TO RETURN A BLANK VALUE TO COLUMN H IF TRUE AND "CHECK" IF FALSE. HAVING TROUBLE GETTING IT TO EITHER CHECK AGAINST ENTIRE COLUMN OR RETURN DESIRED VALUE.


=ISNA(MATCH(E3,$A$3:$A$687821,FALSE))
=IF($A$3:$A$687821=E3," ","check")

THANK YOU
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
HAVE 6 COLUMNS OF DATA, WANT TO MATCH COLUMNS 1 AND 4, 2, AND 5 AND 3 AND 6. WOULD LIKE TO RETURN A BLANK VALUE TO COLUMN H IF TRUE AND "CHECK" IF FALSE. HAVING TROUBLE GETTING IT TO EITHER CHECK AGAINST ENTIRE COLUMN OR RETURN DESIRED VALUE.


=ISNA(MATCH(E3,$A$3:$A$687821,FALSE))
=IF($A$3:$A$687821=E3," ","check")

THANK YOU
=ISNUMBER(MATCH(E3,$A$3:$A$687821,0))+0

would yield 1 for a hit, 0 otherwise. What do you have in E3?
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
Are you wanting to match them all in one formula? If so, maybe:

=if(isna(MATCH(E3,$A$3:$A$687821,FALSE)+MATCH(F3,$B$3:$B$687821,FALSE)+MATCH(G3,$C$3:$C$687821,FALSE)),"Check","")

This would check that all of them had a match or maybe if you only need one of them to have a match try:

=if(or(isnumber(MATCH(E3,$A$3:$A$687821,FALSE)),isnumber(MATCH(F3,$B$3:$B$687821,FALSE)),isnumber(MATCH(G3,$C$3:$C$687821,FALSE)),"","Check")

Also I am not positive if I have the blank and check in the correct spots as I don't completely follow your post.

Hope that helps.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,712
Messages
5,512,992
Members
408,928
Latest member
Black Vinyl

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top