Microsoft Office Forums Multiple criterias search in a table

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-29-2019, 04:14 AM
abc3132 abc3132 is offline Multiple criterias search in a table Windows 7 64bit Multiple criterias search in a table Office 2007
Novice
Multiple criterias search in a table
 
Join Date: Feb 2015
Posts: 24
abc3132 is on a distinguished road
Cool Multiple criterias search in a table


Hi all.
I guess that it was already discussed but I cannot find them.

There is a table:

# Ref € Ref BreakPoint €
A 1 10
A 5 9
B 1 1
B 14 0.2

When you buy 2 units of the reference A the price is 10€
When you buy 8 units of the reference A the price is 9€

What formula should I use to have automatically the right price when I put the reference and the quantity?

Thanks
Reply With Quote
  #2  
Old 11-29-2019, 07:29 AM
ArviLaanemets ArviLaanemets is offline Multiple criterias search in a table Windows 8 Multiple criterias search in a table Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

An example
Attached Files
File Type: xlsx ItemPrices.xlsx (10.7 KB, 5 views)
Reply With Quote
  #3  
Old 11-30-2019, 01:38 AM
abc3132 abc3132 is offline Multiple criterias search in a table Windows 7 64bit Multiple criterias search in a table Office 2007
Novice
Multiple criterias search in a table
 
Join Date: Feb 2015
Posts: 24
abc3132 is on a distinguished road
Default Thanks

Quote:
Originally Posted by ArviLaanemets View Post
An example
That´s is what I want to do. However in the table you gave me I cannot see any formula.How should I do?
Thanks
Reply With Quote
  #4  
Old 11-30-2019, 07:27 AM
ArviLaanemets ArviLaanemets is offline Multiple criterias search in a table Windows 8 Multiple criterias search in a table Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Quote:
Originally Posted by abc3132 View Post
... in the table you gave me I cannot see any formula.How should I do?
I used dynamic defined Names. Look for them in Formulas menu at top of Excel. And there is a formula - the cell value equals with dynamic Name?
Reply With Quote
  #5  
Old 12-01-2019, 05:14 PM
abc3132 abc3132 is offline Multiple criterias search in a table Windows 7 64bit Multiple criterias search in a table Office 2007
Novice
Multiple criterias search in a table
 
Join Date: Feb 2015
Posts: 24
abc3132 is on a distinguished road
Default Thanks

Quote:
Originally Posted by ArviLaanemets View Post
I used dynamic defined Names. Look for them in Formulas menu at top of Excel. And there is a formula - the cell value equals with dynamic Name?
I have tried with what I´ve found in internet but it looks like something is wrong...
Attached Files
File Type: xlsx Breakpoints.xlsx (9.4 KB, 2 views)
Reply With Quote
  #6  
Old 12-02-2019, 02:32 AM
ArviLaanemets ArviLaanemets is offline Multiple criterias search in a table Windows 8 Multiple criterias search in a table Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
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, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search through multiple sheets with multiple textboxes andxie Excel Programming 0 07-09-2018 07:04 PM
Multiple criterias search in a table VBA Table – Search All Tables - Find & Replace Text in Table Cell With Specific Background Color jc491 Word VBA 8 09-30-2015 06:10 AM
formula to find 2 or more criterias then average the sum samtitus Excel 6 02-17-2014 10:16 PM
Multiple criterias search in a table SUMIF/SUMPRODUCT across multiple sheets/Criterias lukeyb_11 Excel 1 02-14-2014 10:30 AM
Multiple criterias search in a table Mail Merge to print specific record based on mergefield criterias nicnad Mail Merge 1 02-22-2012 01:53 AM


All times are GMT -7. The time now is 12:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft