Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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)) |
#2
|
|||
|
|||
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 )) |
#3
|
|||
|
|||
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)) |
#4
|
|||
|
|||
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)) |
#5
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |