Microsoft Office Forums Index and Match Function

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-28-2019, 03:21 PM
mismag mismag is offline Index and Match Function Windows XP Index and Match Function Office 2016
Novice
Index and Match Function
 
Join Date: May 2019
Posts: 3
mismag is on a distinguished road
Default Index and Match Function

Hi,



I am trying to write a formula using Index and Match functions to find the cost based on the type of the vehicle, Sale Price and duration in months.

The problem I am having is there is a range of sale Price.
If the type of vehicle is New,sale price is less than 20000 and term is 48 months then the Cost is $572.
And i don't know how to include the range values in the formula.

Right now the formula I am using is {=INDEX(J8:L19,MATCH(1,(K8:K11=C11)*(J8:J19=C9),0) ,3)}. But with this it is only looking for the specific value, not the range of values.

Any help is greatly appreciated.

Thanks,
Mismag
Attached Images
File Type: jpg Capture.JPG (92.9 KB, 18 views)
Reply With Quote
  #2  
Old 05-28-2019, 11:30 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index and Match Function Windows 7 64bit Index and Match Function Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,383
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
please post a sample sheet. thx
click Go advanced - Manage attachments
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 05-29-2019, 04:04 AM
ArviLaanemets ArviLaanemets is offline Index and Match Function Windows 8 Index and Match Function Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Is this you need?

You can add vehicle classes and price groups whenever you want. Of-course you have then to update costs table too. And when you add vehicle classes, then you have to update price groups too - add columns for new classes. Columns in price groups table must be in same order as vehicle classes in classes table!

In price groups table, prices in all columns must be in growing order! And every price is smallest one in group!

Your original pictured tables were flawed btw. - there were gaps (from 20000 to 21000 in one, from 15000 to 21000 in another).
Attached Files
File Type: xlsx SaleCost.xlsx (15.2 KB, 3 views)
Reply With Quote
  #4  
Old 05-29-2019, 08:49 AM
mismag mismag is offline Index and Match Function Windows XP Index and Match Function Office 2016
Novice
Index and Match Function
 
Join Date: May 2019
Posts: 3
mismag is on a distinguished road
Default

ArviLaanemets,

I really appreciate you taking time to help.

Quote:
Is this you need?
Yes...This is what I need.
But I am having difficulty in breaking down the formula.

What are "nCalcClass,nCalcGroup,nSaleTerm" in the formula?

And also Can I create the all the tables in one sheet instead of in individual sheets?



Thanks,
Mismag
Reply With Quote
  #5  
Old 05-29-2019, 10:52 AM
ArviLaanemets ArviLaanemets is offline Index and Match Function Windows 8 Index and Match Function Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Quote:
Originally Posted by mismag View Post
What are "nCalcClass,nCalcGroup,nSaleTerm" in the formula?
Names. There are more of them - Names (Names are returning a value), and Named Ranges (logically, Named Ranges are returning cell range). You see all of them at Formulas>Name Manager (or something like that - I don't have Excel available currently). Some of them are defined as cell reference, some as Table details (Table column dataranges mostly), and for some are formulas used. Generally I use naming convention where the name of Name is started winth letter 'n', or letter 'l' (Latter I use when name is used as source for Data Validation list. Data validation lists can't work with Table formula syntax, so I defined setup Tables, and then defined Table columns as named ranges).

Quote:
Originally Posted by mismag View Post
And also Can I create the all the tables in one sheet instead of in individual sheets?
You can, but is it a wise move? You work really only with one sheet - with Calulator (or was it Main?). All other sheets are needed only for setup. Really after the setup is done, you can hide all other sheets.

And to have all setup-tables on some single setup sheet makes this sheet cluttered for sure. And only reasonable placement of tables with this setup is to distribute them vertically (so you can delete rows from any setup table without problems, does any need for this arise in future). This means you probably can't see them all at once anyway.

Edit: An afterthought! I am not sure how familiar you are with defined Tables. When you expand defined Table (add rows), all formulas (and formats, data validations, etc.) are expanded automatically too. But only when formula or format is same for whole column! E.g. when you clear one cell anywhere in column with formula, then after that the formula will not expand into new rows, and when you edit the formula in some cell, the edited formula will not expand to other cells in column anymore too.
Reply With Quote
  #6  
Old 05-29-2019, 11:26 AM
mismag mismag is offline Index and Match Function Windows XP Index and Match Function Office 2016
Novice
Index and Match Function
 
Join Date: May 2019
Posts: 3
mismag is on a distinguished road
Default

Thanks a lot ArviLaanemets.
I could find the Name Manager.

It's working now.

I really appreciate your help.

Thanks,
Mismag
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Index and Match function with multiple criteria not searching the next record mushtaqkadar Excel 3 06-17-2017 12:20 AM
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
Index Match Function across different worksheets shay_mt Excel 2 04-27-2015 06:04 AM
Index and Match Function Index Match function jackzha Excel 5 12-03-2014 12:43 PM
Moving a Index/Match function FraserKitchell Excel 2 01-19-2010 09:38 AM


All times are GMT -7. The time now is 10:30 AM.


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