Dynamic Lookup

[fishewb]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5/1/2019
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]OK[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TX[/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CA[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]NY[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for help designing a formula that can lookup data and can be nimble enough to return new data if I change either the date in C1 or the display name in B2:B5.

The display name in column B is a shortened report only display name and is controlled by a separate table used as a basis table that links to all similar reports tables like the one above (one change in the bases tab table and all related cells in display tables will change).

The formula I had built was a SUMProduct that used the model name and looked up in that model. The formula words for editing C1 and finding a new date, but changing a name in B2:B5 won't work as the formula cant use those as a lookup as they aren't the correct value fields in them model, display names only. I'm looking for the ability to type in a new display name in column B or date in C1 or both, and somehow have formulas in C2:C5 update correctly

Thanks for any thoughts on this. Let me know what else I can clarify if this doesn't make sense.

 

[Gerald Higgins]

Can you post the formula that you have now in C2 ?

If your formula and your lookup tables are setup correctly, changing the entries in column B or cell C1 should still see C2:C5 return values for the new entries in B2:B5 or C1.
If this is NOT happening, I think most likely cause is that B2:B5 values are not shown correctly in your look up table, OR C2 value is not shown correctly in your look up table.

 

[fishewb]

Can you post the formula that you have now in C2 ?

If your formula and your lookup tables are setup correctly, changing the entries in column B or cell C1 should still see C2:C5 return values for the new entries in B2:B5 or C1.
If this is NOT happening, I think most likely cause is that B2:B5 values are not shown correctly in your look up table, OR C2 value is not shown correctly in your look up table.

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD][TABLE="class: cms_table_grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5/1/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]OK[/TD]
[TD]510[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TX[/TD]
[TD]690[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CA[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]NY[/TD]
[TD]100

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=SUMPRODUCT((Region Fcst(data model sheet)'!$A$22:$A$290='Basis Tab'!A22)*(Region Fcst'!$J$1:$V$1='Table shown above in this post'!$B$2),Region Fcst'!$J$22:$V$290)

In this formula the Basis Tab A22 corresponds with A2 in the table above, they don't share the same value though.

 

[Gerald Higgins]

Is this SUMPRODUCT formula sitting in B2 in the posted table ?

If YES, is this

'Table shown above in this post'!$B$2

bit correct ? Seems like it would be a circular reference.

Your formula is relatively complex and it's difficult to comment on it without knowing what data is in the ranges that are referred to.

It seems like you want to look up a combination of the date in B1 and the code in A2, and return the corresponding value.
For this to work, the values in B1 and A2 need to be referenced somehow, somewhere else.
I'm guessing they are referenced somehow in your formula, but I don't understand how.

Can you explain please ?

Don't bother too much about explaining the real world application of your spreadsheet - it's probably not too important. Just focus on describing exactly what data you have and what exactly you want to do with it, as clearly as possible.

 

[fishewb]

Thanks for interpreting what I'm trying to get at. I'm going to try and explain exactly what I'm trying to do without real world application info.

Sheets/Tables Formula working with.

[TABLE="class: grid, width: 837"]
<colgroup><col><col span="2"><col><col><col><col><col span="2"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]End Result (ER)-
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Data Model (DM)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Basis Lookup Template (BLT)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C…[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C…[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD="align: right"]5/1/2019[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]6/1/2019[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]6/1/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]OK[/TD]
[TD="align: right"]510[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]Oklahoma ltd.[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]500[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]Oklahoma ltd.[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TX[/TD]
[TD="align: right"]690[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]Texas Whipple Inc.[/TD]
[TD="align: right"]690[/TD]
[TD="align: right"]700[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]Texas Whipple Inc.[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CA[/TD]
[TD="align: right"]300[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD]California Dust..[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD]California Dust..[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]NY[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]NY - (ltd)[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]NY - (ltd)[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Formula currently in B2 of "ER" table above
SUMPRODUCT((DM'!$A$2:$A$5='BLT'!A2)*(DM'!$B$1:$G$1='ER'!$B$1),DM'!$A$2:$C$5)

I am seeking a look up a combination of the date in B1 and the code in A2, and return the corresponding value from the DM sheet. BLT is used as a single location to change a name and have it flow to each ER table (there are many). I'm stuck because I can't use the longer value in DM A2 and BLT A2, I need to have the shortened value in ER A2 showing instead.

 

[Gerald Higgins]

OK, 2 things.

FIRST, if you have to use the 2 character codes in ER col A, and cross reference them against the longer names in BLT col A, I think you need some kind of look up table to do that element by itself.
Can you add another column to BLT to store the 2 character codes ?

SECOND, I don't think you need SUMPRODUCT for this, I think you can do this perhaps with a combination of OFFSET and INDEX/MATCH.

 

[fishewb]

OK, 2 things.

FIRST, if you have to use the 2 character codes in ER col A, and cross reference them against the longer names in BLT col A, I think you need some kind of look up table to do that element by itself.
Can you add another column to BLT to store the 2 character codes ?

SECOND, I don't think you need SUMPRODUCT for this, I think you can do this perhaps with a combination of OFFSET and INDEX/MATCH.

Yes I can add to BLT and make a lookup in that. if I did that what would an Offset Index/Match look like potentially.

 

[Gerald Higgins]

OK so I'm going to guess that you create a new column B in table BLT, after your long names, before your dates, to contain the 2 character codes.

I'll post back with a suggestion.

 

[Gerald Higgins]

=offset(dm!$a$1,match(index(blt!$a$2:$a$5,match(er!$a2,blt!$b$2:$b$5,0)),dm!$a$2:$a$5,0),match(er!b$1,dm!$b$1:$c$1,0),1,1)

 

[fishewb]

Thank you so much! I've never worked with offset and really appreciate you showing me this option.