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]

Did it work ?

 

[fishewb]

Yes, it works perfectly.

 

[Gerald Higgins]

Yay !

 

[fishewb]

Yes, it works perfectly.

Well one more variable I want to ask about.

Would this theoretically work if set up correctly if there is non contiguous data in a couple ranges? I wasn't able to replicate that, it was one reason why I was using SUMProduct as it could look across noncontinuous data.

 

[fishewb]

Well one more variable I want to ask about.

Would this theoretically work if set up correctly if there is non contiguous data in a couple ranges? I wasn't able to replicate that, it was one reason why I was using SUMProduct as it could look across noncontinuous data.

[TABLE="class: cms_table_grid, width: 837"]
<tbody>[TR]
[/TR]
[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]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TX[/TD]
[TD="align: right"]690[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/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]CA[/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]
[/TR]
</tbody>[/TABLE]

I removed data above to replicate the structure of what I'm trying to describe.

 

[fishewb]

[TABLE="class: cms_table_grid, width: 837"]
<tbody>[TR]
[/TR]
[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]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TX[/TD]
[TD="align: right"]690[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/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]CA[/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]
[/TR]
</tbody>[/TABLE]

I removed data above to replicate the structure of what I'm trying to describe.

Nevermind...I was not building the formula correctly. It works perfectly, amazing really! Thank you again so much.

I was not defining my starting point for the offset correctly, when I did that correctly boom! Everything works perfectly.