09-09-2003 02:03									#1
	Need Excel help Support Apolyton, buy Civilization: The Boardgame

I've been tasked with creating a basic Excel program (although this probably isn't the correct term), but I've only ever used it about twice before in my life. What I need to do is this:

I need to write a program that allows you to input a number (six figure, if it's important), which is then checked against a list of six figure numbers to see if it matches any of them. It then needs to say if it does or not. Presentation isn't important (not at the moment, anyway), it just needs to work. So far, I have the numbers entered into one of the columns, but my Excel knowledge ends roughly about here.

Little help, anyone? Please?

	09-09-2003 02:09									#2
	Help yourself to an AD-FREE life

=match(A3,B1:B100,0)

where a3 is where the inputted number is typed in and B1:B100 where the list of numbers that you want to see if there is any matches.

If there is a match, a number will result which indicates how far down the list the matched number is.

If there isn't a match, you will get "N/A".

if you want a "TRUE" or "FALSE" answer then modify the formula to

=if(isna(match(a3,B1:B100,0))=false,"MATCH","NO MATCH")

You can also replace the B1:B100 with just the column to get the entire column scanned (eg match(a3,B:B,0))

Last edited by Sharpe on 09-09-2003 at 02:16

	09-09-2003 02:12									#3
	Put an end to popups!

Or, you can use the "or" function

	09-09-2003 02:28									#4
	Got spare money?

Sorry, thanks for the help and all, but you'll have to dumb it down a bit because I keep getting dialogue boxes about "circular references". Let me give you a bit more info so you can tailor the formula for me, because Christ knows, I can't do it myself.

Lets say I want the number to be entered in box B1, and the true/false answer in box B2. The numbers I want checked are in column A (A1 to A19 at the moment, although I may need to expand this in the future).

Is that enough to modify the formula for me? Thanks in advance to anyone who can sort it out for me.

	09-09-2003 02:44									#5
	Lose 30 kilos (of popups)

With the "Or" fxn (takes a little time, Sharpe's is better for lots of number to compare to)

Put your numbers in A1-A19

In B2:

Highlight field (or just click on it)
Go to Insert -> fxn
Select Logical, A box will open up

"logical 1" B1=A1
"logical 2" B1=A2
"logical 3" B1=A3
etc...

When your done with all logic options OK
Then when you enter a number in B1, B2 will return "True" if it matches the number or "False" if it doesn't.

	09-09-2003 02:47									#6
	Support Apolyton or Terrorists Win

For Sharpe's answer

Put this in B2:

=if(isna(match(B1,B1:B19,0))=false,"MATCH","NO MATCH")

	09-09-2003 02:52									#7
	Support Apolyton, buy Call to Power 2

Sorry, which logical function am I using here?

	09-09-2003 02:55									#8
	Support Apolyton, buy Civilization 2

Never mind, I got Sharpe's method to work with the alterations you gave me (and one or two other minor ones). Thanks a lot, guys.

Mods, you can close this if you want.

	09-09-2003 02:57									#9
	Put an end to popups!

There's two different suggestions here. The "or" function is the easiest.

quote: Put your numbers in A1-A19 In B2: Highlight field (or just click on it) Go to Insert -> fxn Select Logical, A box will open up "logical 1" B1=A1 "logical 2" B1=A2 "logical 3" B1=A3 etc... When your done with all logic options OK Then when you enter a number in B1, B2 will return "True" if it matches the number or "False" if it doesn't.

Yet if you have a really long list, Sharpe's "If" function is easier.

quote: Put this in B2: =if(isna(match(B1,B1:B19,0))=false,"MATCH","NO MATCH")

With the "B1:B19" indicating that list. Oh, it should be "A1:A19".

quote: =if(isna(match(B1,A1:A19,0))=false,"MATCH","NO MATCH")

[/quote]

	09-09-2003 03:01									#10
	Support Apolyton, buy Civilization III: Complete

Yeah, I noticed the column thing when I tried to get it to work. No big deal.

I just figured Sharpe's method would be better if I had to expand the list at any point in the future, so I just edited A1:A19 to A:A, like he mentioned earlier, and it works fine.

	09-09-2003 03:02									#11
	Support Apolyton, buy Civilization: The Boardgame

kewl

I had never used the "match" thing 'till today. I'm still messing with it

	09-09-2003 03:04									#12
	Increase the size of your Attachments

I think we've both learned something today.

	09-09-2003 03:48									#13
	Support Apolyton

Well on my excel file for work I have been making a lot of use of VLOOKUP