Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Excel formula help

#1 wdc202 Posted 06 July 2006 - 03:36 PM

wdc202

Member

• Member
• 259 posts
Given an Excel worksheet with numbers in cells A1 through A100 and in cells B1 through B100

What formular do I insert in C1 to print "Match" in C1 if A1 is equal to any value in the B column? That is if A1=B1 or A1=B2 or A1=B3 or ...A1=B100?

Thanks.
• 0

#2 Octagonal Posted 06 July 2006 - 06:11 PM

Octagonal

Member 2k

• Member
• 2,528 posts
Hi wdc202,

The formula that you could use is

=IF(ISNA(MATCH(A1,\$B\$1:\$B\$20,0)),"","Match")

Do you understand the formula?

Hope this helps.

Edited by Octagonal, 06 July 2006 - 06:12 PM.

• 0

#3 wdc202 Posted 07 July 2006 - 11:04 AM

wdc202

Member

• Topic Starter
• Member
• 259 posts
Thanks Octagonal. I kind-of-see the formula but not fully. I am not familiar with ISNA function. Could you spell out just what it is doing? Thanks much. wdc
• 0

#4 Octagonal Posted 07 July 2006 - 08:13 PM

Octagonal

Member 2k

• Member
• 2,528 posts
Hi wdc202,

I'll see if I can explain this so it doesn't sound too complicated.

The MATCH(A1,\$B\$1:\$B\$20,0) searches the range of \$B\$1 to \$B\$20 for a match of cell A1. The 0 value searches for an exact match.

You can have a value of 1 which searches for the largest value less than or equal to the value that you are comparing with.

You can have a value of -1 which searches for the smallest value greater than or equal to the value that you are comparing with.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

Now ISNA looks to see if the Match part returns a N/A result.

Then the IF looks to see what the ISNA returns a true or false value and determines whether to show a blank cell or put the word Match in the cell.

Type Match into the help bar of Excel, this gives a pretty good description of what the keyword does.

BTW You should change the \$B\$20 to \$B\$100 for your worksheet. I used \$B\$20 in the test that I ran.

• 0

#5 wdc202 Posted 08 July 2006 - 09:30 AM

wdc202

Member

• Topic Starter
• Member
• 259 posts
Thanks a million for the very clear explanation. ... wdc
• 0

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users