View Single Post
 
Old 12-02-2019, 02:32 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

I edited my example so it works now with your data. 1 and A returns 9, 15 and B returns 20 (9 you got is false!)


How it works:


On sheet Items you have 2 Tables (To define a Table: Enter headers for Table, select them and a row or some below of headers, from INSERT menu select Table, check 'My Table Has Headers', and edit Table Name in DESIGN menu bar. Press Enter!), tItemLimits, and tItemPrices.


Into Table tItemLimits you enter in growing order all possible limits for any of items, The number of fields in this Table is limited by allowed number of columns in worksheet. The Table has a single Datarow, and all fields must be filled. When there is more fields in this Table then different limits, then you copy the biggest one until last column.


Into field Item of Table tItemPrices you enter the list of all your items (a row per item). And then for every item into all quantity fields you enter the price starting from value of aligned limit in tIlemLimits Table. To skip a limit, you repeat latest price for given item.


As next, you select the list of items in Item field of tItemPrices Table, select Name Manager from FORMULAS menu, And create a new Name based on selected range. I named this Name as lItems (l as List).


Now on sheet Test (or whatever you name it), into column A you enter what parameters you will enter (like 'Reference:' and 'Quantity:'. and what you want to get like 'Price:').


Then you select the cell for Item/Reference/whatever, from DATA menu select Data Validation, select List in Allow field of Data Validation form , and enter formula like '=lItems' into Source field. OK. Now you can enter only items, which match exactly with ones in items listed in tItemPrices. And you also can select those items.


After that you set Names for Item/Reference and Quantity fields (e.g. B1 and B2). You select the cell, and enter the name into Name box, or you activate Name Manager and define the Name there. I used Names 'nTestItem' and nTestQty', but you can use anything unless it is blocked by Excel. When you want to edit the Name later, then you can do this in Name Manager only.


Now you activate Name Manager and create a Name nTestPrice. Into 'Refers to' field of Name Manager form you enter formula
Code:
=INDEX(tItemPrices,MATCH(nTestItem,tItemPrices[Item],0),MATCH(nTestQty,tItemLimits,1))
, and click Close.


It's done.


Instead defining Tables, you also can define Named ranges for datarange of item prices table and item limits table, and for Item column in item prices table, and use those Named Ranges instead of Table references.


About your formulas:
The one in cell C9 - I can't fathom what you are trying there!
The one in C1:
=MATCH(B2,E2:E6,0) returns 4;
=MATCH(A2,F2:F7;0) returns 2;
=AND(4,2,0) returns False, which is same as 0;
=INDEX(G2:G6;0) always returns 1st item in list, i.e. 9
Attached Files
File Type: xlsx ItemPrices.xlsx (10.7 KB, 6 views)
Reply With Quote