Forum Discussion
Index Match with Variable Column
I need to treat some data to upload to a system. I have TAB 1 with company names, expense type and expense amount, and TAB 2 with company names and system values based on the expense type for each co...
How about this:
Scenario Recap
- TAB 1 contains:
- Company Name, Expense Type, and Expense Amount.
- TAB 2 contains:
- Company Name (in rows) and System Values (in columns based on Expense Type).
Formula Breakdown
You can use INDEX to fetch the value and XMATCH to dynamically locate the appropriate row and column in TAB 2:
Formula:
=INDEX(TAB2!$B$2:$E$100, XMATCH(A2, TAB2!$A$2:$A$100, 0), XMATCH(B2, TAB2!$B$1:$E$1, 0))