Formula Modification Help
 

Hello,

I need to modify the lookup formula below because what it now looks for has
the letters IM in front of the munbers. Example IM1780 where before they were
straight numbers. Example 4160048

I would like to say If (H3779,PSWRDREQ,2) is equal to IM....then only give
me the numbers to the right of the IM. Example 1780....

Can you plese help? The formula as it is now is:
=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",VL OOKUP(H3779,PSWRDREQ,2,FALSE))

one way:

=IF(ISNA(MATCH("IM" & H3779, PSWRDREQ, FALSE)), "", VLOOKUP("IM" &
H3779, PSWRDREQ, 2, FALSE))




In article ,
Dmorri254 wrote:

Hello,

I need to modify the lookup formula below because what it now looks for has
the letters IM in front of the munbers. Example IM1780 where before they were
straight numbers. Example 4160048

I would like to say If (H3779,PSWRDREQ,2) is equal to IM....then only give
me the numbers to the right of the IM. Example 1780....

Can you plese help? The formula as it is now is:
=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",VL OOKUP(H3779,PSWRDREQ,2,FALSE
))

Do you mean?

=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",IF (LEFT(VLOOKUP(H3779,PSWRDR
EQ,2,FALSE),2)="IM",MID(VLOOKUP(H3779,PSWRDREQ,2,F ALSE),3,32),""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dmorri254" wrote in message
...
Hello,

I need to modify the lookup formula below because what it now looks for
has
the letters IM in front of the munbers. Example IM1780 where before they
were
straight numbers. Example 4160048

I would like to say If (H3779,PSWRDREQ,2) is equal to IM....then only
give
me the numbers to the right of the IM. Example 1780....

Can you plese help? The formula as it is now is:

=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",VL OOKUP(H3779,PSWRDREQ,2,FAL
SE))

Hmm...this approach did not work....lets see if I can be more clear....I need
it to say If the number is IM whatever, ignore the IM and just give me the
numbers...

Does this help? Thank you so much...

David

"Bob Phillips" wrote:

Do you mean?

=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",IF (LEFT(VLOOKUP(H3779,PSWRDR
EQ,2,FALSE),2)="IM",MID(VLOOKUP(H3779,PSWRDREQ,2,F ALSE),3,32),""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dmorri254" wrote in message
...
Hello,

I need to modify the lookup formula below because what it now looks for
has
the letters IM in front of the munbers. Example IM1780 where before they
were
straight numbers. Example 4160048

I would like to say If (H3779,PSWRDREQ,2) is equal to IM....then only
give
me the numbers to the right of the IM. Example 1780....

Can you plese help? The formula as it is now is:

=IF(ISERROR(VLOOKUP(H3779,PSWRDREQ,2,FALSE)),"",VL OOKUP(H3779,PSWRDREQ,2,FALSE))

Perhaps


=IF(ISNA(MATCH(--MID(H3779,1+2*(LEFT(H3779)="IM"),255),PSWRDREQ,0)) ,"",
VLOOKUP(--MID(H3779,1+2*(LEFT(H3779,2)="IM"),255),PSWRDREQ,2 ,FALSE))

This assumes that H3779 may or may not be prefixed with IM, and that
your lookup range is numeric. 255 is just a big number

In article ,
Dmorri254 wrote:

Hmm...this approach did not work....lets see if I can be more clear....I need
it to say If the number is IM whatever, ignore the IM and just give me the
numbers...

Does this help? Thank you so much...