Jump to content

Welcome to Geeks to Go - Register now for FREE

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
Photo

Excel formula help


  • Please log in to reply

#1
wdc202

wdc202

    Member

  • Member
  • PipPipPip
  • 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

Advertisements


#2
Octagonal

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 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. :whistling:

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

  • 0

#3
wdc202

wdc202

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 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

Octagonal

    Member 2k

  • Member
  • PipPipPipPipPip
  • 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.

:whistling:
  • 0

#5
wdc202

wdc202

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 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

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP