Referencing cells in multiple columns separated by multiple criteria columns

Barren_Wuffett

New Member
Joined
Aug 6, 2019
Messages
18
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.

ABCDEFGH
1
2Tenant Name$$$
3A9716384
4A3791863
5A8941257
6B528494
7B3453294
8B6523653
9C3913995
10C1837359
11C9440662
12
13
14Letter:B
15Number:1
16
17$ Number:

<tbody>
</tbody>

(Sorry, my work computer didn't allow me to use the tool to post a screenshot, so I hope the above works)

Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top