Value Replacement Formula (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

You can enter this formula in C2:

=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE))

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

=IF(ISERROR(VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE)),"",VLOOKUP(B2,Sheet2!$A$1:$B$3,2,FALSE))

Reply | Quote

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.

Reply | Quote