Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value Replacement Formula (Excel 2003)

    I am trying to use a formula that enters a numeric value that corresponds to specified text values referenced.
    Sheet 1 data table:
    Priority Priority Sort
    Task 1 High
    Task 2 Medium
    Task 3 Low
    Task 4 High
    Task 5 High
    Task 6 High
    Task 7 Medium

    Sheet 2 reference table is:
    High 1
    Medium 2
    Low 3

    I started playing with the following formula: =IF(ISBLANK(B2),"High",IF(ISERROR(LOOKUP(B2,Sheet! 2A1:B3,)),"Medium",LOOKUP(B3,Sheet2!A1:B3)))

    That obviously doesn't work, but I'm sure you see what I am trying to do.

    I want Column C (Priority Sort) of Sheet1 to reflect Sheet2 Column B values based on Sheet 1 Column B values. I think that makes sense.

    jb
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Value Replacement Formula (Excel 2003)

    You can enter this formula in C2:
    <code>
    =IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$1:$B$3,2,F ALSE))
    </code>
    and fill down. If you want to avoid #N/A if the user enters an incorrect value in column B (e.g. Hugh instead of High), use
    <code>
    =IF(ISERROR(VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE)), "",VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE))</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Value Replacement Formula (Excel 2003)

    If I am not mistaken, is this not just a VLOOKUP to collect the value of HIGH< MEDIUM and LOW's value in sheet 2. If my logic is incorrect, please let me know.

    The formula: =VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE)

    See sample.
    Attached Files Attached Files
    Regards,
    Rudi

Posting Permissions

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