Referencing cells in multiple columns separated by multiple criteria columns
Results 1 to 5 of 5

Thread: Referencing cells in multiple columns separated by multiple criteria columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Referencing cells in multiple columns separated by multiple criteria columns

    Hey everyone, I'm trying to fill cell C17 by pulling values from columns C, E, or G using criteria C14 from criteria column B and criteria C15 from criteria columns D, F, and H. Example, if criteria are B and 1, then I'd want to pull cell E7 as shown below. I know it'll probably be some complex variation of index/match, array, etc. but I can't figure it out without using helper columns.
    A B C D E F G H
    1
    2 Tenant Name $ $ $
    3 A 97 1 63 84
    4 A 37 91 86 3
    5 A 89 41 2 57
    6 B 5 2 84 94
    7 B 34 53 2 94
    8 B 65 23 65 3
    9 C 39 1 39 95
    10 C 18 37 3 59
    11 C 94 40 66 2
    12
    13
    14 Letter: B
    15 Number: 1
    16
    17 $ Number:
    (Sorry, my work computer didn't allow me to use the tool to post a screenshot, so I hope the above works) Thanks!

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Referencing cells in multiple columns separated by multiple criteria columns

    Why are you pulling E7?

    Does Letter A relate to column C, letter B relate to column E, letter C to column G?
    That's fine, but the 1st number that relates to B is E6 not E7.

    So why ware you pulling E7?

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing cells in multiple columns separated by multiple criteria columns

    Sorry, F7 should say 1. The Number criteria is supposed to pull from the column to the left.

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Referencing cells in multiple columns separated by multiple criteria columns

    OK, question not answered properly.

    1. Does Letter A relate to column C, letter B relate to column E, letter C to column G?
    2. What does the 1 against "Number:" represent?

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referencing cells in multiple columns separated by multiple criteria columns

    A-H in the top row represent columns and 1-17 represent rows like in Excel. So cells C14 and C15 (next to "Letter:" and "Number:" respectively) are my criteria. Letter:B (C14) is a tenant name. Which should be found in cells B3:B11. Number:1 (C15) is a number found in either D3:D11, F3:F11, or H3:H11, and then offset one column to the left to pull from one of the $ columns (columns C, E, or G).

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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