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 company. I need to find a way to look at the expense type in TAB 1 and return the value from the right column in TAB 2. So far, the only solution I could come up with is using nested IF formulas, but that is not very efficient. I saw the possibility of using INDEX and multiple XMATCH, but I was not able to make that work. Does anybody know an efficient way to do this, without having to use macros?
I've created a spreadsheet with an example of what I mean. The cells in blue are the ones I need to fill.
https://docs.google.com/spreadsheets/d/1zUGgMxc1dWJmG64zxx_PTpJeKoZynGnzEQw4g9gqSSM/edit?usp=sharing
2 Replies
In the attached workbook, you can see additional possibilities. Be sure to read the _Info worksheet.
(I had attached the workbook; this editor now seems to be randomly dropping attachments.)
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))
