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

1. ## 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. ## 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. ## 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. ## Re: Referencing cells in multiple columns separated by multiple criteria columns

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. ## 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).