Need Excel help

[Paul Hanson]

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?

[Sharpe]

=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))

[Japher]

Or, you can use the "or" function

[Paul Hanson]

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.

[Japher]

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.

[Japher]

For Sharpe's answer

Put this in B2:

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

[Paul Hanson]

Sorry, which logical function am I using here?

[Paul Hanson]

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.

[Japher]

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

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.

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".

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

[/quote]

[Paul Hanson]

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.

[Japher]

kewl

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

[Paul Hanson]

I think we've both learned something today.

[Provost Harrison]

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