Results 1 to 11 of 11
  1. #1
    Star Lounger
    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.

  2. #2
    Gold Lounger
    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

  3. #3
    Star Lounger
    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]

  4. #4
    Gold Lounger
    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 ?.

  5. #5
    Star Lounger
    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.

  6. #6
    Gold Lounger
    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
    Attached Files Attached Files

  7. #7
    Star Lounger
    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.

  8. #8
    Gold Lounger
    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

  9. #9
    Uranium Lounger
    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

  10. #10
    Gold Lounger
    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

  11. #11
    Uranium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •