Forum Discussion
Multiple Table Lookup From Multiple Criteria
Trying to figure out Multiple Table Lookup From Multiple Criteria. I've attached an image. I'm looking for M8. If the series changed to 'B', then the value would be 23. So I want the lookup to change tables based off the Series and Class, then find the look up of row and column and return that value.
I hope that came out clear, lol.
I was going to use an index, switch and xmatch, but I can't get my head around it.
Thanks, Greg
I think any way you approach this task there's going to be some legwork done to make the data whole for lookup purposes. I used INDEX with several named items:
=LET( a, XMATCH(Series & " Class " & Class, tables), i, XMATCH(Row, SeriesAClass150[COL.ROW]), j, XMATCH(Column, SeriesAClass150[#Headers]), return, IFNA( INDEX( ( SeriesAClass150, SeriesAClass300, SeriesAClass400, SeriesBClass150, SeriesBClass300, SeriesBClass400 ), i, j, a ), "" ), return )
4 Replies
I think any way you approach this task there's going to be some legwork done to make the data whole for lookup purposes. I used INDEX with several named items:
=LET( a, XMATCH(Series & " Class " & Class, tables), i, XMATCH(Row, SeriesAClass150[COL.ROW]), j, XMATCH(Column, SeriesAClass150[#Headers]), return, IFNA( INDEX( ( SeriesAClass150, SeriesAClass300, SeriesAClass400, SeriesBClass150, SeriesBClass300, SeriesBClass400 ), i, j, a ), "" ), return )Thank you Patrick! This worked. I had to research LET a bit, and I think I understand the storing calculations now.
You're welcome. You may step through the calculations by changing the last instance of "return" to a, i, or j, for example to view the store calculations.
Hi Patrick2788,
This is what I was looking for, but I am a little confused on the usage of a, i, j and return in your formula? I've never seen that before. Do they have to be in there?
