Results 1 to 11 of 11
Thread: Conditional Statement

20010419, 19:49 #1
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Conditional Statement
Hi,
I have a set of data that spans over a range of cells, A50:B150. Column A contains names and column B contains numbers. The data is arranged in no particular order.
At the top of my worksheet, I have a table. Column 1 is the name of a station, and column 2 is its number. I am trying to write a conditional statement that will work as follow: If "station name" (A2) equals the station name in my data, get the value of the cell beside it and put it in column 2 of the table.
Could anyone help me with the equation?
Thanks in advance, Hanan.

20010419, 20:03 #2
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
The VLOOKUP function seems to be what you are looking for. If I understand you problem, you need the following formula in B2 :<pre> =VLOOKUP(A2,$A$50:$B$150,2,FALSE)</pre>
It might be a good idea to name the data range (a50:b150) as Database for instance, so the above formula would read ::<pre> =VLOOKUP(A2,Database,2,FALSE)</pre>
in the above example if the name in A2 is not found in the data you will get a #N/A result.
Hope that helps you,
Andrew C

20010419, 20:24 #3
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Hi Andrew,
Not exactly what I wanted. A50:A150 contain names of stations in random order. B50:B150 contain number of stations that correspond to the station name. For instance,
Station Name Station Number

upper_level 5
lower_lever 1
floor 0
What I want is a way to allow me to create a table such that if I write "lower_lever" in A1 I will get the number "0" in B1, and so on.
The reason that I need it is because I have 100 names and and I only need 20 from this array. So that once I put the station name in my table I will get its corresponding value. By the way, station names in the array (A50:A150) appear only once, hence, are unique.
Hope I made myself clearer, Hanan. [img]/forums/images/smilies/smile.gif[/img]

20010419, 20:45 #4
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Are you saying that if you enter "lower_lever" in A1, you want the number of "floor" (0) in B1 ?.

20010420, 11:53 #5
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
No Andrew. If I enter "lower_lever" in A1, I would like to see the number that is to the right of this station. In this case it will be "1", and it will be printed in cell B1. For the same example above, if I enter "upper_level" in A2, I would like to see the number "5" in B2. The number "5" in this case is the number to the right of the station name.
Maybe to make it clearer, I should have my table on another sheet, like sheet2, and my data in sheet1.
Hope this helps, Hanan.

20010420, 12:22 #6
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Hanan, If that is what you want, then I think VLOOKUP should work. I am attachibg a workbook with that solution, and if it is not suitable perhaps you could explain what it is doing wrong. I have extended the formula so that #N/A does not show when A is blank.
Andrew

20010420, 12:34 #7
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Andrew, you are amazing...
This is exacly what I wanted. Thank you very much. [img]/forums/images/smilies/smile.gif[/img]
I have two questions for you, if you don't mind. Could you please explain the following formula to me since I have no idea what it does or means:
=IF(ISNA(VLOOKUP(A2,Stations,2,FALSE)),"",VLOOKUP( A2,Stations,2,FALSE))
Also I noticed that this formula works independent of lower/upper casing of the station name. How did you do that?
Thanks again, Hanan.

20010420, 12:55 #8
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Hanan,
Actually I am not amazing at all  the function is built into excel. The important part is =VLOOKUP(A2,Stations,2,FALSE) which does the following :
1 get the value in A2,
2 look for a match in Stations (A50:B150)
 the station names must be in the first column (A in this case)
3 and if found return the value in Column 2 of stations
4 FALSE means find an exact match,
 if that is TRUE or left blank the nearest match is found.
The ISNA bit first checks to see if a match for the value in A can be found and if not just enters a null value. Actually a better formula might be =IF(A2="","",VLOOKUP(A3,Stations,2,FALSE)), because if you enter an invalid name in A it will return #N/A so you will know there is something wrong with just a quick glance.
The fact that is does not matter about upper/lower case is due to Microsoft  not me. Hope that clears it up for you. The only bit you really need is the expression in bold above. You should play around with it and look up the help file, as the lookup functions can be very usefull.
Andrew

20010420, 13:34 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Conditional Statement
Hanan, when FALSE is used as the last argument to VLOOKUP to require an exact match, it also has the corrolary effect that the source table does not need to be sorted (by station name in this case), which is the reason to use FALSE in this example and many other applications.
John ... I float in liquid gardens
UTC 7ąDS

20010420, 13:37 #10
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Conditional Statement
Thanks for that John, I should have pointed that out as Hanan did say his data was not in any particular order.
Andrew

20010420, 14:04 #11
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Conditional Statement
It has always annoyed me that Excel Help doesn't point the "sort not required" corrolary of using FALSE in VLOOKUP, which to me is the more important consequence.
John ... I float in liquid gardens
UTC 7ąDS