+ Reply to Thread
Results 1 to 19 of 19

If Cell=A, start looking down in row, find X and return C

  1. #1
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Angry If Cell=A, start looking down in row, find X and return C

    Probably the worst title possible, but its hard to describe. My issue is a simple VLOOKUP- but it needs a 3rd parameter- but I can't do a nested VLOOKUP cos the first column may be blank! Ready to pull out my hair.

    So, here's an example:

    Data!

    Work | Person A | Profit1
    _____| Person C | Profit2
    _____| Person B | Profit3
    _____| Person D | Profit4
    Home | Person L | Profit5
    _____| Person A | Profit6
    School| Person X | Profit7
    _____| Person B | Profit8
    _____| Person A | Profit9


    So, I want a cell on another page to print out data. So lets say I have it to give me person A profit at Home. If I do VLOOKUP('Person A',Data!A:C,3,FALSE) Excel gives me Profit 1. I want it to give me Profit 6. So I can nest it, but it will only do that if cells in Column A were all labeled. They're not-- and they're variable- some have 4 people, some have 2- so I can't use a offset.

    Basically- I need it too look down the first column (place-home school work) find a variable, then start looking from that row down to find a person (personA) then print the corresponding profit.

    It seems so easy, but that extra 3rd step without the ID's working in Column A has thrown me off. I have it working with a MATCH nested in an INDEX- but it fails because of the blanks in column A.

    I realize this was a nightmare to read, but i'm running out of options here! Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If Cell=A, start looking down in row, find X and return C

    you can insert a column to the left of the Profit column, with a formula like: (assuming you have a new column C)

    =IF(A2="",C1,A2)&"_"&B2 copied down.

    Then for you Vlookup, you would use a lookup value like =VLOOKUP("Home_Person A",Data!C:D,2,FALSE)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by NBVC View Post
    you can insert a column to the left of the Profit column, with a formula like: (assuming you have a new column C)

    =IF(A2="",C1,A2)&"_"&B2 copied down.

    Then for you Vlookup, you would use a lookup value like =VLOOKUP("Home_Person A",Data!C:D,2,FALSE)
    Thank you-- A variation of that would work, but would not be optimal- as I receive the raw data every day and the profit column is whats changing. I could do an =IF function to just make the names show up over and over again... Thanks for the idea in case I can't get the original function to work for me!

    To put into context, the original column B in my document is 944 entries, with 240 duplicates- so Id like as little as i can mess with that as possible.
    Last edited by artistapart; 09-29-2016 at 03:43 PM. Reason: size of document

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If Cell=A, start looking down in row, find X and return C

    Another way to autofill column A:

    Select column A and then hold the CTRL key and select A1.

    Then go to Edit|Go To and click Special....select Blanks and click Ok.

    enter an = sign and hit the up arrow key.

    Hold the CTRL key again and hit ENTER.

    You may have to fix whatever was in A1.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Cell=A, start looking down in row, find X and return C

    Why don't you fill in the blanks in column A:

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Work
    Person A
    Profit1
    ------
    Person A
    Home
    Profit6
    2
    Work
    Person C
    Profit2
    3
    Work
    Person B
    Profit3
    4
    Work
    Person D
    Profit4
    5
    Home
    Person L
    Profit5
    6
    Home
    Person A
    Profit6
    7
    School
    Person X
    Profit7
    8
    School
    Person B
    Profit8
    9
    School
    Person A
    Profit9


    This array formula** entered in G1:

    =INDEX(C1:C9,MATCH(1,(B1:B9=E1)*(A1:A9=F1),0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by NBVC View Post
    Another way to autofill column A:

    Select column A and then hold the CTRL key and select A1.

    Then go to Edit|Go To and click Special....select Blanks and click Ok.

    enter an = sign and hit the up arrow key.

    Hold the CTRL key again and hit ENTER.

    You may have to fix whatever was in A1.
    Huh.... thats a neat trick... Wish I could rep you twice for that one.

    Well, thats a much better fallback! Is it just not possible to do what I want to do?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If Cell=A, start looking down in row, find X and return C

    It would be a more complicated formula like:

    =INDEX(INDEX(Data!$A$1:$C$9,MATCH("Home",Data!$A$1:$A$9,0),1):Data!$C$9,MATCH("Person A",INDEX(Data!$B$1:$C$9,MATCH("Home",Data!$A$1:$A$9,0),1):Data!$B$9,0),3)

    where you would replace "Home" and "Person A" with cell references containing those items. Also you would need to use a defined range so that the bottom of the range is definite... This way you would not need to change your column A....

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by artistapart View Post
    Is it just not possible to do what I want to do?
    It's possible but it's more complicated...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Work
    Person A
    Profit1
    ------
    Person B
    School
    Profit8
    2
    Person C
    Profit2
    3
    Person B
    Profit3
    4
    Person D
    Profit4
    5
    Home
    Person L
    Profit5
    6
    Person A
    Profit6
    7
    School
    Person X
    Profit7
    8
    Person B
    Profit8
    9
    Person A
    Profit9


    This formula entered in G1:

    =LOOKUP(2,1/(B1:B9=E1)/(LOOKUP(ROW(A1:A9),ROW(A1:A9)/(A1:A9<>""),A1:A9)=F1),C1:C9)

  9. #9
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by NBVC View Post
    It would be a more complicated formula like:

    =INDEX(INDEX(Data!$A$1:$C$9,MATCH("Home",Data!$A$1:$A$9,0),1):Data!$C$9,MATCH("Person A",INDEX(Data!$B$1:$C$9,MATCH("Home",Data!$A$1:$A$9,0),1):Data!$B$9,0),3)

    where you would replace "Home" and "Person A" with cell references containing those items. Also you would need to use a defined range so that the bottom of the range is definite... This way you would not need to change your column A....
    This may be more in line with what i'm trying to do--

    where you would replace "Home" and "Person A" with cell references containing those items.
    This I already have- i have a few data validation lists on the presentation page to make it user friendly- i didn't say that at first to keep it simple

    Also you would need to use a defined range so that the bottom of the range is definite... This way you would not need to change your column A....
    Therein lies my problem

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If Cell=A, start looking down in row, find X and return C

    Ok, try this.

    In a cell of your results sheet enter formula like: =MATCH(REPT("z",255),Data!B:B) this finds last row in column B of Data sheet containing a text string..

    Then change my formula to:

    =INDEX(INDEX(Data!$A$1:INDEX($C:$C,$X$2),MATCH("Home",INDEX(Data!$A:$A,$X$2),0),1):INDEX(Data!$C:$C,$X$2),MATCH("Person A",INDEX(Data!$B$1:INDEX(Data!$C:$C,$X$2),MATCH("Home",Data!$A$1:INDEX($A:$A,$X$2),0),1):INDEX(Data!$B:$B,$X$2),0),3)

    Where each occurance of X2 should be replaced with cell reference where you entered the first formula...

  11. #11
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by Tony Valko View Post
    This formula entered in G1:

    =LOOKUP(2,1/(B1:B9=E1)/(LOOKUP(ROW(A1:A9),ROW(A1:A9)/(A1:A9<>""),A1:A9)=F1),C1:C9)

    this is going to sound so much more elementary of a question than what i'm working with:

    This works- but what is the 2,1 referencing? In my actual document this does not kick back any errors- but it only gives me 0%, which isn't around the destination cell. I feel like this is the right answer, i'm just missing something- i understand the entire formula except that bit.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Cell=A, start looking down in row, find X and return C

    This will generate an array of either 1 or #DIV/0! errors:

    1/(B1:B9=E1)/(LOOKUP(ROW(A1:A9),ROW(A1:A9)/(A1:A9<>""),A1:A9)=F1)

    Based on the sample data in post #8 that array would look like this:

    {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}

    The way that LOOKUP works is if the lookup value of 2 is greater than any other number in the array it will match the last number in the array.

    The last (only) number in the array is the 1.

    So, the result of the formula is the cell in C1:C9 that corresponds to the 1:

    C1: #DIV/0!
    C2: #DIV/0!
    C3: #DIV/0!
    C4: #DIV/0!
    C5: #DIV/0!
    C6: #DIV/0!
    C7: #DIV/0!
    C8: 1
    C9: #DIV/0!

    C8 = Profit 8

    So:

    =LOOKUP(2,1/(B1:B9=E1)/(LOOKUP(ROW(A1:A9),ROW(A1:A9)/(A1:A9<>""),A1:A9)=F1),C1:C9)

    = Profit 8

  13. #13
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: If Cell=A, start looking down in row, find X and return C

    If you have all category only once in column A, then you can use this normal formula.
    Please Login or Register  to view this content.
    See the attachment.
    Attached Files Attached Files
    Last edited by sanram; 09-29-2016 at 06:50 PM.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by sanram View Post
    =INDEX(INDEX(Sheet1!$C:$C,MATCH($B2,Sheet1!$A:$A,0)):Sheet1!$C$9,MATCH($A2,INDEX(Sheet1!$B:$B,MATCH($B2,Sheet1!$A:$A,0)):Sheet1!$B$9,0))
    Using that formula on this data leads to incorrect results.

    Data Range
    A
    B
    C
    1
    North
    Site1
    Joe
    2
    Site2
    Tom
    3
    Site3
    Tina
    4
    Site4
    Lisa
    5
    East
    Site1
    Greg
    6
    Site2
    Bill
    7
    South
    Site1
    Bob
    8
    West
    Site1
    Tracy
    9
    Site2
    Linda
    10
    Site3
    Pete
    11
    Site4
    Paul
    12
    Site5
    Trip


    If we look for South and Site3 the formula returns Pete when it should have returned an error.

  15. #15
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: If Cell=A, start looking down in row, find X and return C

    Thanks for showing this. I didn't keep that in mind during creating that formula.

  16. #16
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by sanram View Post
    If you have all category only once in column A, then you can use this normal formula.
    Please Login or Register  to view this content.
    See the attachment.
    This is the one!!!!!!

    Worked perfectly with a bit of tweaking to the specifics of my chart and Tony's correction!! Both of you:::THANK YOU THANK YOU THANK YOU

    THANK YOU!

  17. #17
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: If Cell=A, start looking down in row, find X and return C

    You are welcome and thanks for the rep. But be sure to take care of Tony Valko's suggestion. Otherwise you will get wrong answer.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: If Cell=A, start looking down in row, find X and return C

    Kinda' what I had in post # 7, but you said the defining of the bottom of the ranges was going to be a problem....

  19. #19
    Registered User
    Join Date
    12-19-2013
    Location
    13 54636556
    MS-Off Ver
    Excel 2011
    Posts
    30

    Re: If Cell=A, start looking down in row, find X and return C

    Quote Originally Posted by NBVC View Post
    Kinda' what I had in post # 7, but you said the defining of the bottom of the ranges was going to be a problem....
    Yup, it was my comprehension of your suggestion that was the problem! +REP to all involved. Thanks!!

+ 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] Find last non-blank cell in a column then used it as Start of a range
    By bhenlee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2015, 06:27 PM
  2. Find start and Next row with text and create Cell Address in groups
    By 2k05gt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2015, 10:06 PM
  3. Need a Formula Find sections in a column and return start and end times
    By john dalton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2014, 05:42 AM
  4. [SOLVED] VBA to return the column numbers of the start and finish of a merged cell? RANDOM!
    By JamesGoulding85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2013, 01:32 PM
  5. [SOLVED] Find, move to another cell on same line, insert value , and start all over again
    By Ioannis77 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2013, 04:46 AM
  6. Replies: 4
    Last Post: 01-15-2013, 03:19 PM
  7. [SOLVED] Find match, find related cell and return that number
    By HelpHelpHelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2012, 06:46 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