How to use dynamic variables in an Index & Match function?

Hey Guys,

First time poster so i apologise if i'm doing this incorrectly/breaking any rules.

Basically, i've got the below code and it works just fine but it's not the most elegant solution and i was wondering if any of you had any bright ideas on how to tidy this up a little?

Basically, i have three columns, A & B are data validation lists. B is a dynamic list depending on what is next to it in A. The code below sits in column C.

On another sheet i have a bunch of 2-column tables. List A displays all of the tables and list B will then list the rows entries of one of those two columns of the table you select with list A. The code below will then display whatever is in the second column (of the table and row you select with list A and List B).

=IFERROR(INDEX((IF(T15='Business Units'!B$2,CorpBU,(IF(T15='Business Units'!B$3,DMSBU,(IF(T15='Business Units'!B$4,ESBU,(IF(T15='Business Units'!B$5,ESSBU,(IF(T15='Business Units'!B$6,JOBBU,(IF(T15='Business Units'!B$7,NDISBU,(IF(T15='Business Units'!B$8,NPABU,(IF(T15='Business Units'!B$9,OHBU,(IF(T15='Business Units'!B$10,PEBU,(IF(T15='Business Units'!B$11,PHaMsBU,(IF(T15='Business Units'!B$12,RASBU,(IF(T15='Business Units'!B$13,WCBU,(IF(T15='Business Units'!B$14,WFDBU,(IF(T15='Business Units'!B$15,YEPBU,"")))))))))))))))))))))))))))),MATCH($P15,(IF(T15='Business Units'!B$2,Corp,(IF(T15='Business Units'!B$3,DMS,(IF(T15='Business Units'!B$4,ES,(IF(T15='Business Units'!B$5,ESS,(IF(T15='Business Units'!B$6,JOB,(IF(T15='Business Units'!B$7,NDIS,IF(T15='Business Units'!B$8,NPA,(IF(T15='Business Units'!B$9,OH,(IF(T15='Business Units'!B$10,PE,(IF(T15='Business Units'!B$11,PHaMs,(IF(T15='Business Units'!B$12,RAS,(IF(T15='Business Units'!B$13,WC,(IF(T15='Business Units'!B$14,WFD,(IF(T15='Business Units'!B$15,YEP,""))))))))))))))))))))))))))),0),1),"")

Note: in the above code, entries that have the suffix BU (i.e CorpBU) are a table and anything that doesn't have 'BU' (i.e. Corp) is a named range. These named ranges are a column in each of the 2-column tables. For example; The 2-column table called 'CorpBU' has two columns, One called 'GL code' and one called 'Finance Code'. The named Range 'Corp' refers to the 'Finance Code' column of 'CorpBU'. These tables are business units.

I tried playing around with index & match but i found i couldn't figure out a way to dynamically change the array variable.

For example what i would have liked is =index(~dynamic variable from List A/a business unit table name~,Match(cell referencing a business unit table name,~dynamic variable from list B~,0),1).

Clear as mud?

I appreciate that sounds really confusing but i hope you get where im going with that.

Like i said, the code works but ideally i would like to simplify that code and even more ideally, have that code more dynamic because as it stands, if i add another business unit table, all that code will have to be altered for every cell that code is situated in.

EDIT: I've attached a cleansed version of the data and what I have. It works but i would like a more elegant way to get the data in column Q on the 'startrack' sheet.

Thanks in advance.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Try

Q6

Formula:

Please Login or Register  to view this content.

Copy down!

You might also use
=IF(OR([@[Code: IT ONLY]]="",[@[Branch/
Site Name:]]=""),"",IFERROR(INDEX(INDIRECT([@[Code: IT ONLY]]&"BU"),MATCH($P6,INDIRECT([@[Code: IT ONLY]]),0),1),""))

I suspect that if you were to use one table rather than several on the Branch Sites sheet, you could make it much more efficient.

Shukla's reply (#4) gives you the flexibility and removes the need to have named ranges.

I added a helper column on the 'Startrack' worksheet (starting in cell U6) with the following formula (this was referred to as MainData[@Column1] in the updated formula in Q6 below). I couldn't enclose it in 'CODE tags as the formula itself includes the word code which messed up the formatting):

=SUMPRODUCT(--(BusinessUnits

Please Login or Register  to view this content.

However, my suggestion is somewhat inflexible in that if additional BU's are added my formula would need to be updated. I'd agree with prior posts that a reworking to facilitate more flexible VLOOKUPS or INDEX / MATCH functions would be better again.

Removed by JT