+ Reply to Thread
Results 1 to 19 of 19

V lookup and if statement

  1. #1
    Registered User
    Join Date
    03-09-2016
    Location
    states
    MS-Off Ver
    2003
    Posts
    52

    V lookup and if statement

    Hello

    Can someone help me with the following

    I want a formula put in that shows if column d has the value p1 in it, add in the sector number

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,970

    Re: V lookup and if statement

    You can put this formula in E2:

    =IF(RIGHT(D2,2)="P1","SECTOR="&A2,"")

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: V lookup and if statement

    You haven't said where you want the formula.

    If I was inserting the formula in column E then the formula would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This formula searches D2 for data that is in B2. if it finds it it brings through the data in cell A2. If it doesn't find it the cells remains blank.

    This will allow the formula to be copied down your sheet.

    I think Pete's formula will only look for P1 and only look at the last 2 characters in cell D2 (so wouldn't find P10 or P11).
    Last edited by Declamatory; 10-28-2016 at 06:20 AM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,970

    Re: V lookup and if statement

    @Declamatory,

    That picks up P10 and P11.

    Pete

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    My guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanation

  7. #7
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: V lookup and if statement

    Sorry Pete, it does but incorrectly. Cell E3 should have a result of nothing but it has a value of SECTOR=293291 because it is bringing through the Sector for attribute P10 when Cell D3 has an attribute of P1.

    The formula doesn't take into account the attributes changing in column B such as P5

  8. #8
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: V lookup and if statement

    My guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanation
    Then my formula would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    @Declamatory@ your formula checks row by row and, effectively, matches the Attribute on that row. It's not looking up the table in columns A:B

    Note that the original file has over a million "blank" rows, hence the size of the file. I have deleted them.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: V lookup and if statement

    Ahh, I see TMS. Good job.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    @Declamatory: thanks for the feedback and rep, much appreciated.

    All we need now is for the OP to come back and let us know what he's really trying to do

  12. #12
    Registered User
    Join Date
    03-09-2016
    Location
    states
    MS-Off Ver
    2003
    Posts
    52

    Re: V lookup and if statement

    Thanks for this. I cannot seem to get it working

  13. #13
    Registered User
    Join Date
    03-09-2016
    Location
    states
    MS-Off Ver
    2003
    Posts
    52

    Re: V lookup and if statement

    The formula does not appear to be looking at the whole table

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,751

    Re: V lookup and if statement

    Re-post a file showing what is not working.

  15. #15
    Registered User
    Join Date
    03-09-2016
    Location
    states
    MS-Off Ver
    2003
    Posts
    52

    Re: V lookup and if statement

    I need it to pick up the whole table with the correct numbers

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,970

    Re: V lookup and if statement

    You need to change the formula in E2 to this:

    =INDEX($A:$A,MATCH(RIGHT($D2,LEN(D2)-(FIND("=",$D2,1))),$B:$B,0))

    Use Ctrl-Shift-Enter to commit it, rather than the usual < Enter >, as it is an array formula, and then copy it down as required.

    Hope this helps.

    Pete

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    This works but it's NOT an array formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It looks as though you have changed the text prior to the code.
    Last edited by TMS; 11-02-2016 at 08:16 AM.

  18. #18
    Registered User
    Join Date
    03-09-2016
    Location
    states
    MS-Off Ver
    2003
    Posts
    52

    Re: V lookup and if statement

    how do I keep the word sector in it?

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,273

    Re: V lookup and if statement

    Something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup and if statement help
    By Excel15 in forum Excel General
    Replies: 5
    Last Post: 02-05-2016, 12:15 PM
  2. If statement and V Lookup
    By sansan88 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2016, 08:52 AM
  3. [SOLVED] If statement and/or lookup help
    By FirestarZA in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-12-2014, 08:44 AM
  4. IF statement and lookup
    By mwhiteley in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-18-2013, 02:10 PM
  5. Lookup Statement
    By seanyc in forum Excel General
    Replies: 8
    Last Post: 06-21-2012, 08:01 AM
  6. LOOKUP within a IF statement
    By Coors in forum Excel General
    Replies: 17
    Last Post: 05-06-2011, 04:05 PM
  7. IF statement with V LOOKUP
    By bigduke6 in forum Excel General
    Replies: 3
    Last Post: 08-26-2010, 10:11 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1