Jaymond Flurrie
Well-known Member
- Joined
- Sep 22, 2008
- Messages
- 919
- Office Version
- 365
- Platform
- Windows
I have a target table and a list of values. The table should be filled based on the list.
Here's what it is normally: Two Way Lookup In Excel
So I have that one backwards:
And the source list is in the M1:M4. And the target is A1:K12. Both of these can be a lot bigger too (the list can have, say, 10000 values and the table can be 2000 rows. The column size is at least on the source list fixed).
The cells in A1:K12 should have some kind of formula, that reads from the source list that whether the given cell has a value or not. So, cell B2 result should be 5, cell C2 should result should be 3 and cell D3 result should be 8. The same row-column value shouldn't be defined more than once in the M1:O4 list.
This is trivial to do with VBA. But how about with Excel-formulas? I was trying something with VLOOKUP and INDEX-MATCH, but didn't really get a good solution with those.
So again, looking for a formula for those A1:K12 cells.
I'm using Excel 2016.
Here's what it is normally: Two Way Lookup In Excel
So I have that one backwards:
And the source list is in the M1:M4. And the target is A1:K12. Both of these can be a lot bigger too (the list can have, say, 10000 values and the table can be 2000 rows. The column size is at least on the source list fixed).
The cells in A1:K12 should have some kind of formula, that reads from the source list that whether the given cell has a value or not. So, cell B2 result should be 5, cell C2 should result should be 3 and cell D3 result should be 8. The same row-column value shouldn't be defined more than once in the M1:O4 list.
This is trivial to do with VBA. But how about with Excel-formulas? I was trying something with VLOOKUP and INDEX-MATCH, but didn't really get a good solution with those.
So again, looking for a formula for those A1:K12 cells.
I'm using Excel 2016.