+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

Hybrid View

  1. 03-05-2011, 11:27 AM #1
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Hi, I am using Vlookup, but I have a problem, I have 2 identical items within my range.

    If I have as follows:
    IC Prov 145.00
    IC Prov 150.00

    My formula is as follows:
    =IF(ISNA(VLOOKUP("IC Prov   ",$I$16:$X$83,15,FALSE)),"",VLOOKUP("IC Prov   ",$I$16:$X$83,15,FALSE))
    How can I modify the formula to ignore the first "IC Prov" and return the value on the second "IC Prov" without having to change the range?

    Thanks
    Last edited by sonar123; 03-05-2011 at 06:53 PM.
    Reply With QuoteRegister To Reply
  2. 03-05-2011, 01:27 PM #2
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    A bit messy, but try 
    =IF(ISNUMBER(MATCH("IC Prov   ",INDIRECT("I"&16+MATCH("IC Prov   ",I16:I83,0)&":I83"),0)),INDEX($I$16:$X$83,MATCH("IC Prov   ",INDIRECT("I"&16+MATCH("IC Prov   ",I16:I83,0)&":I83"),0)+MATCH("IC Prov   ",I16:I83,0),15),"")

    Why are there 3 spaces trailing your Lookup value "IC Prov"?
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    Reply With QuoteRegister To Reply
  3. 03-05-2011, 01:49 PM #3
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    There is space trailing it because it is that way in the ascii format that gets converted into excell, I managed to fix that by using * after the word to subsitute spaces etc. before the last ".

    Thanks, will try the formula.

    By the way, can you please explain that extra insert?

    Thanks
    Reply With QuoteRegister To Reply
  4. 03-05-2011, 02:03 PM #4
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    The formula targeted the first IC Prov, how do I get it to return a figure on the second one and not the first one?
    Reply With QuoteRegister To Reply
  5. 03-05-2011, 02:19 PM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Quote Originally Posted by sonar123
    The formula targeted the first IC Prov, how do I get it to return a figure on the second one and not the first one?
    Marcol's suggestion should work ok as I see it - returning the appropriate value from Col W

    Another alternative would be to adopt a basic SMALL based non-volatile Array:

    =IF(COUNTIF($I$16:$I$83,"IC Prov   ")<2,"",INDEX($X$16:$X$83,SMALL(IF($I$16:$I$83="IC Prov   ",ROW($I$16:$I$83)-ROW($I$16)+1),2)))
confirmed with CTRL + SHIFT + ENTER
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  6. 03-05-2011, 03:16 PM #6
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Hi, thanks, but I have tried it and it still does not target the second IC Prov.

    I have attached the spreadsheet.
    The total of IC Prov of R33251.94 (cell W34) must be returned in cell E21, not R33251.94 (cell W21)

    I know it displays the same figure as provident fund for employee and co should be the same figure. But I have encountered where it is not.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 03-05-2011, 03:20 PM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Perhaps not the best example given both values cited are the same, however, the Array will work if you modify the INDEX range to W rather than X:

    =IF(COUNTIF($I$16:$I$83,"IC Prov   ")<2,"",INDEX($W$16:$W$83,SMALL(IF($I$16:$I$83="IC Prov   ",ROW($I$16:$I$83)-ROW($I$16)+1),2)))
confirmed with CTRL + SHIFT + ENTER
    Marcol's original suggestion works without issue also.
    Reply With QuoteRegister To Reply
  8. 03-05-2011, 04:01 PM #8
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Thanks a mill. it works perfectly.
    Reply With QuoteRegister To Reply
  9. 03-05-2011, 05:27 PM #9
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Red face Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Can the formula 
    {=IF(COUNTIF($I$1:$I$83,"IC Prov   ")<2,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov   ",ROW($I$1:$I$83)-ROW($I$1)+1),2)))}
    be modified so if there is only one IC Prov, it does not return nothing, but returns the first one it sees? And can we eliminate the space trailing IC Prov ?

    Just using IC Prov as an example. Most other items appears only once and I would like to modify the formula to use for all off them.

    Thanks
    Reply With QuoteRegister To Reply
  10. 03-05-2011, 05:52 PM #10
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Quote Originally Posted by sonar123
    Can the formula be modified so if there is only one IC Prov, it does not return nothing, but returns the first one it sees?
    Perhaps

    =IF(COUNTIF($I$1:$I$83,"IC Prov   ")=0,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov   ",ROW($I$1:$I$83)-ROW($I$1)+1),MIN(2,COUNTIF($I$1:$I$83,"IC Prov   ")))))
confirmed with CTRL + SHIFT + ENTER
    Regards removing trailing spaces - possibly but that rather depends on the exclusivity of the search terms (use of wildcards in the COUNTIF etc)
    Reply With QuoteRegister To Reply
  11. 03-05-2011, 06:52 PM #11
    sonar123
    sonar123 is offline
    Registered User
    Join Date
    03-05-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"

    Thanks once again. You were a great help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1