Excel gurus - DFWstangs Forums
 
LinkBack Thread Tools Display Modes
post #1 of 14 (permalink) Old 06-02-2009, 01:39 PM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
Excel gurus

Ok I want to look at a cell with a number in it and compare it to another cell with a number in it and if the cells are the same numbers I want it to write a text and if it is not correct test the next cell...

example:

If a1 = a2 then write " P.O. " otherwise if a1 = a3 then write "P.O. 2"

and continue this test till it finds the correct argument any help is appreciated!

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
Sponsored Links
Advertisement
 
post #2 of 14 (permalink) Old 06-02-2009, 02:48 PM
Resident Misanthrope
 
Big Studly's Avatar
 
Join Date: Jun 2006
Location: I am sorry, you must have mistaken me for someone that gives a Fuck!
Posts: 9,690
try

=IF (a1=a2, P.O., if (a1=a3, P.O., if (a1=a3, P.0, if (a1=a4, P.O., if (a1=a5, P.O. etc...)))))

it is a long ass function

here is one from a spreadhseet that I use looking for a maximum value

=IF(C28<=0.25,0.9,IF(C28<=0.35,0.8,IF(C28<=0.45,0.7,IF(C28<=0.55,0.6,0.5))))

Big Studly is offline  
post #3 of 14 (permalink) Old 06-02-2009, 03:00 PM
Time Served
 
Join Date: Jul 2004
Location: Plano
Posts: 711
Quote:
Originally Posted by Big Studly View Post
try

=IF (a1=a2, P.O., if (a1=a3, P.O., if (a1=a3, P.0, if (a1=a4, P.O., if (a1=a5, P.O. etc...)))))

it is a long ass function

here is one from a spreadhseet that I use looking for a maximum value

=IF(C28<=0.25,0.9,IF(C28<=0.35,0.8,IF(C28<=0.45,0.7,IF(C28<=0.55,0.6,0.5))))
this way will work and will probably be easier to understand but takes a lot of time to type out if you are comparing a really large number of rows.

you could try using vlookup or hlookup (vlookup if the data is in columns, hlookup if it's in rows) and it would require a lot less typing, but requires a bit more knowledge. if you are used to using databases then vlookup is kinda like a simple scalar query.

'01 Bullitt #0054
bullitt54 is offline  
 
post #4 of 14 (permalink) Old 06-02-2009, 03:04 PM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
Thanks for the response seems like this method might work as well

http://www.techonthenet.com/excel/formulas/vlookup.php

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
post #5 of 14 (permalink) Old 06-02-2009, 03:07 PM
Time Served
 
Join Date: Jul 2004
Location: Plano
Posts: 711
i attached an example for vlookup.

the only formula is under the result column. columns A and B are your data columns (where you are going to match and lookup stuff).

change the value under 'match this'

when you change that number, the vlookup formula will find that number in column A and then return the number in column B.

hope this helps.
Attached Files
File Type: zip vlookup example.zip (4.4 KB, 26 views)

'01 Bullitt #0054
bullitt54 is offline  
post #6 of 14 (permalink) Old 06-02-2009, 03:09 PM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
=IF(ISNA(VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,2,FALSE))," ",VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,2,FALSE))

I have come up with this, but for some reason I am not getting a return of anything

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
post #7 of 14 (permalink) Old 06-02-2009, 03:10 PM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
Quote:
Originally Posted by bullitt54 View Post
i attached an example for vlookup.

the only formula is under the result column. columns A and B are your data columns (where you are going to match and lookup stuff).

change the value under 'match this'

when you change that number, the vlookup formula will find that number in column A and then return the number in column B.

hope this helps.

Thank ya I will look at this....

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
post #8 of 14 (permalink) Old 06-02-2009, 07:45 PM
Time Served
 
Join Date: Jul 2004
Location: Plano
Posts: 711
Quote:
Originally Posted by DFWminis View Post
=IF(ISNA(VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,2,FALSE))," ",VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,2,FALSE))

I have come up with this, but for some reason I am not getting a return of anything
try this:

=IF(ISNA(VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,1,FALSE))," ",VLOOKUP(A23,'Account Numbers'!$A$2:$A$500,1,FALSE))

the 2nd to last argument you had has a "2". that will return the 2nd column in the range of cells you selected A2 - A500. Since that range is only one column you need a "1" in there instead.

usually a vlookup is used when you have at least two columns. the first column in the range acts as your index. that determines what row it is going to look at and try to find a match for. the 2nd to last argument tells it what column on that row to look at for the return value. for instance if you had A2 - B500, and you put a 2 in there it would find the matching value in column A, and then return the value that is in column B.

i can't think of any really good reason to use just one column, if you already know the value you want to look up, A23 in your case, then you would just use that value.

'01 Bullitt #0054
bullitt54 is offline  
post #9 of 14 (permalink) Old 06-03-2009, 10:10 AM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
Sent ya a Pm bullit

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
post #10 of 14 (permalink) Old 06-03-2009, 11:52 AM
Lifer
 
Avery'sDad's Avatar
 
Join Date: Feb 2008
Location: Red Oak
Posts: 1,509
It almost looks like your trying to find duplicate values and only need it from a visual standpoint. You might be better off using "conditional formatting". If your using 2007 here is a screenshot.
Attached Thumbnails
Click image for larger version

Name:	Clipboard01.jpg
Views:	28
Size:	71.0 KB
ID:	92175  
Avery'sDad is offline  
post #11 of 14 (permalink) Old 06-03-2009, 11:58 AM
Aspiring Bean Counter.
 
Slowhand's Avatar
 
Join Date: Feb 2006
Location: Howard Johnson's Earthlight Room
Posts: 12,279
vlookup is the way to go for that. Follow the advice given above and you'll be good.

Slowhand is offline  
post #12 of 14 (permalink) Old 06-03-2009, 12:01 PM
Lifer
 
Join Date: Apr 2006
Location: Fate, TX
Posts: 1,014
Can you post an example of the worksheet you are working on right now to give us a better idea of what you actually need it to do?
Mach1Nut is offline  
post #13 of 14 (permalink) Old 06-03-2009, 05:25 PM Thread Starter
Long Live T.S.
 
DFWminis's Avatar
 
Join Date: Feb 2004
Location: F U thieves!
Posts: 4,765
I was using the wrong function... bullit helped me out I needed to us HLOOKUP not vlookup

To me, clowns aren't funny. In fact, they're kind of scary. I've wondered where this started and I think it goes back to the time I went to the circus, and a clown killed my dad.
DFWminis is offline  
post #14 of 14 (permalink) Old 06-03-2009, 06:41 PM
Lifer
 
Join Date: Apr 2006
Location: Fate, TX
Posts: 1,014
Quote:
Originally Posted by DFWminis View Post
I was using the wrong function... bullit helped me out I needed to us HLOOKUP not vlookup
Right on.

<--- Excel Nerd.
Mach1Nut is offline  
Sponsored Links
Advertisement
 
Reply

Bookmarks

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the DFWstangs Forums forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome