Lookup from two independent tables

[bcbuck88]

Hello,

I'm wondering if I can produce a lookup based on two separate tables. I would like these to work independently of each other. A user may select from table 1, a bill code and enter a cost. Currently that populates a result in table 3. I'd like to populate a result in table 3 bases of the discretion of the user. They may either choose from table 1 or 2 and find the same results. Any guidance would be most appreciated!

Thanks!
Brian

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 435"]
<tbody>[TR]
[TD]Table 1: Search by Bill Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Step 1: Choose Bill Code[/TD]
[TD]Select from Drop Down [/TD]
[/TR]
[TR]
[TD]Step 2: Enter Cost[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2: Search by Product Type [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Step 1: Choose Service Line[/TD]
[TD]Select from Drop Down[/TD]
[/TR]
[TR]
[TD]Step 2: Choose Supply Type [/TD]
[TD]Select from Drop Down[/TD]
[/TR]
[TR]
[TD]Step 3: Enter Cost [/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Table 3: Results [/TD]
[/TR]
[TR]
[TD]Unique Item Code [/TD]
[TD]Lookup based on Bill Code and Cost (B3 & B4) or Supply Type and Cost (B13 & B14) [/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Example of lookup source:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 501"]
<tbody>[TR]
[TD] Supply Type [/TD]
[TD] Bill Code [/TD]
[TD] Supply Min Cost [/TD]
[TD] Supply Max Cost [/TD]
[TD] Unique Item Code [/TD]
[/TR]
[TR]
[TD]Hand Tool [/TD]
[TD]C1883[/TD]
[TD] $ 1 [/TD]
[TD] $ 500 [/TD]
[TD="align: right"] 75021156[/TD]
[/TR]
[TR]
[TD]Hand Tool [/TD]
[TD]C1883[/TD]
[TD] $ 501 [/TD]
[TD] $ 1,000 [/TD]
[TD="align: right"]75021157[/TD]
[/TR]
[TR]
[TD]Hand Tool [/TD]
[TD]C1883[/TD]
[TD] $ 1,001 [/TD]
[TD] $ 1,500 [/TD]
[TD="align: right"]75021924[/TD]
[/TR]
[TR]
[TD]Hand Tool [/TD]
[TD]C1883[/TD]
[TD] $ 1,501 [/TD]
[TD] $ 2,000 [/TD]
[TD="align: right"]75024241[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 1 [/TD]
[TD] $ 250 [/TD]
[TD="align: right"]75016905[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 251 [/TD]
[TD] $ 500 [/TD]
[TD="align: right"]75029476[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 501 [/TD]
[TD] $ 750 [/TD]
[TD="align: right"]75029477[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 751 [/TD]
[TD] $ 1,000 [/TD]
[TD="align: right"]75029478[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 1,001 [/TD]
[TD] $ 1,250 [/TD]
[TD="align: right"]75029479[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 1,251 [/TD]
[TD] $ 1,500 [/TD]
[TD="align: right"] 75029480[/TD]
[/TR]
[TR]
[TD]Blade[/TD]
[TD]L2620[/TD]
[TD] $ 1,501 [/TD]
[TD] $ 1,750 [/TD]
[TD="align: right"]75029481[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

if the user enters C1883 and $700 as a cost - how do you want to proceed ?

 

[bcbuck88]

C1883 and $700 would crosswalk to item number 75021157. Do you know of such a formula that can determine if the lookup should be in column A or B determined by the criteria selected in the two tables above?

[TABLE="class: cms_table, width: 501"]
<tbody>[TR]
[TD]Supply Type [/TD]
[TD] Bill Code [/TD]
[TD] Supply Min Cost [/TD]
[TD] Supply Max Cost [/TD]
[TD] Unique Item Code [/TD]
[/TR]
[TR]
[TD]Hand Tool[/TD]
[TD]C1883[/TD]
[TD] $ 1[/TD]
[TD] $ 500[/TD]
[TD="align: right"] 75021156[/TD]
[/TR]
[TR]
[TD]Hand Tool[/TD]
[TD]C1883[/TD]
[TD] $ 501[/TD]
[TD] $ 1,000[/TD]
[TD="align: right"]75021157[/TD]
[/TR]
[TR]
[TD]Hand Tool[/TD]
[TD]C1883[/TD]
[TD] $ 1,001[/TD]
[TD] $ 1,500[/TD]
[TD="align: right"]75021924[/TD]
[/TR]
[TR]
[TD]Hand Tool[/TD]
[TD]C1883[/TD]
[TD] $ 1,501[/TD]
[TD] $ 2,000[/TD]
[TD="align: right"]75024241[/TD]
[/TR]
</tbody>[/TABLE]