Home |
Search |
Today's Posts |
#1
|
|||
|
|||
combining a Vlookup and Mid formula
Searched the groups but can't find any examples.
The following formula works fine but I want to add a few things. In cell B3 =IF('Base'!E7="",VLOOKUP($H3,OTIS!$BG:$BJ,4,FALSE) ,VLOOKUP($F3,OTIS!$R:$W,4,FALSE)) On the OTIS worksheet in columns BJ and W, I have cells with A123BC in them. I only want to return the MID of the cell. =MID(BJ1,2,3) is what I want extracted. Also, If H3 and F3 are both blank, the cell displays #N/A. When I want the cell to show blank. I've tried NOT(ISNA and nesting another IF, but just can't get it right. So what I'm trying to get is: if E7="", lookup the mid 3 numbers from OTIS!column BJ, else lookup the mid 3 numbers from OTIS!column W, and if both H3 and F3 are blank, then supress the #na and show a blank. |
#2
|
|||
|
|||
combining a Vlookup and Mid formula
Hi!
So what I'm trying to get is: if E7="", lookup the mid 3 numbers from OTIS!column BJ, else lookup the mid 3 numbers from OTIS!column W, and if both H3 and F3 are blank, then supress the #na and show a blank. Do you mean you want to RETURN the mid 3 from those columns? =IF('Base'!E7="",VLOOKUP($H3,OTIS!$BG:$BJ,4,FALSE) ,VLOOKUP($F3,OTIS!$R:$W,4,FALSE)) In the second lookup, column W is not the 4th column in that table_array. I think it'd be easier to use 2 additional columns and extract the mid 3 then use that column in the lookup. But, this is what you asked for: =IF(AND(F3="",H3=""),"",IF(Base!E7="",MID(VLOOKUP( $H3,OTIS!$BG:$BJ,4,FALSE),2,3),MID(VLOOKUP($F3,OTI S!$R:$W,4,FALSE),2,3))) Biff wrote in message ups.com... Searched the groups but can't find any examples. The following formula works fine but I want to add a few things. In cell B3 =IF('Base'!E7="",VLOOKUP($H3,OTIS!$BG:$BJ,4,FALSE) ,VLOOKUP($F3,OTIS!$R:$W,4,FALSE)) On the OTIS worksheet in columns BJ and W, I have cells with A123BC in them. I only want to return the MID of the cell. =MID(BJ1,2,3) is what I want extracted. Also, If H3 and F3 are both blank, the cell displays #N/A. When I want the cell to show blank. I've tried NOT(ISNA and nesting another IF, but just can't get it right. So what I'm trying to get is: if E7="", lookup the mid 3 numbers from OTIS!column BJ, else lookup the mid 3 numbers from OTIS!column W, and if both H3 and F3 are blank, then supress the #na and show a blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|