combining a Vlookup and Mid formula

[augrection]

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.

 

[Biff]

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,OTIS!$R:$W,4,FALSE),2,3)))

Biff