Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Complex Index Match Help (or at least complex to me)

I have a index match that needs additional criteria. The formula
references a separate sheet called "INPUTS" and is referencing a number
associated with a price that looks like this:

cost size
..80 124
..90 148
1.00 156
(and so on)

here is the formula
INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) -
the multiple at the end is a total quantity. the b6 is the size

Here is what I am stuck with. I want to add another criteria for the
quantity. So something that would add in match (quantity less than
30000,quantity greater than 30,000 but less than 40,000, greater than
40,000 but less than 50,000)

at the end of the day I am looking for something to do this: If the
size is 124 AND the quantity is 30,000, then multiply the quantity by
..80)

maybe it should be VBA.

thanks for any help!!!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Complex Index Match Help (or at least complex to me)

=INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46=
30000)*(Inputs!$C$4:$C$46<40000),0))*B36

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer Reitman" wrote in message
ups.com...
I have a index match that needs additional criteria. The formula
references a separate sheet called "INPUTS" and is referencing a number
associated with a price that looks like this:

cost size
.80 124
.90 148
1.00 156
(and so on)

here is the formula
INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) -
the multiple at the end is a total quantity. the b6 is the size

Here is what I am stuck with. I want to add another criteria for the
quantity. So something that would add in match (quantity less than
30000,quantity greater than 30,000 but less than 40,000, greater than
40,000 but less than 50,000)

at the end of the day I am looking for something to do this: If the
size is 124 AND the quantity is 30,000, then multiply the quantity by
.80)

maybe it should be VBA.

thanks for any help!!!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Complex Index Match Help (or at least complex to me)

Hmm. but the <=30,000 (and series of numbers) was also going to be in
the table as a 3rd column, matching another cell (like the match for
b6)
so my question was how to add another match and index into the formula
so there would be two matching criteria further complicated by the less
than, greater than aspect.

Bob Phillips wrote:
=INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46=
30000)*(Inputs!$C$4:$C$46<40000),0))*B36

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer Reitman" wrote in message
ups.com...
I have a index match that needs additional criteria. The formula
references a separate sheet called "INPUTS" and is referencing a number
associated with a price that looks like this:

cost size
.80 124
.90 148
1.00 156
(and so on)

here is the formula
INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) -
the multiple at the end is a total quantity. the b6 is the size

Here is what I am stuck with. I want to add another criteria for the
quantity. So something that would add in match (quantity less than
30000,quantity greater than 30,000 but less than 40,000, greater than
40,000 but less than 50,000)

at the end of the day I am looking for something to do this: If the
size is 124 AND the quantity is 30,000, then multiply the quantity by
.80)

maybe it should be VBA.

thanks for any help!!!


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Complex Index Match Help (or at least complex to me)

layout some data for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer Reitman" wrote in message
oups.com...
Hmm. but the <=30,000 (and series of numbers) was also going to be in
the table as a 3rd column, matching another cell (like the match for
b6)
so my question was how to add another match and index into the formula
so there would be two matching criteria further complicated by the less
than, greater than aspect.

Bob Phillips wrote:

=INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46=
30000)*(Inputs!$C$4:$C$46<40000),0))*B36

which is an array formula, it should be committed with Ctrl-Shift-Enter,

not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer Reitman" wrote in message
ups.com...
I have a index match that needs additional criteria. The formula
references a separate sheet called "INPUTS" and is referencing a

number
associated with a price that looks like this:

cost size
.80 124
.90 148
1.00 156
(and so on)

here is the formula
INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) -
the multiple at the end is a total quantity. the b6 is the size

Here is what I am stuck with. I want to add another criteria for the
quantity. So something that would add in match (quantity less than
30000,quantity greater than 30,000 but less than 40,000, greater than
40,000 but less than 50,000)

at the end of the day I am looking for something to do this: If the
size is 124 AND the quantity is 30,000, then multiply the quantity by
.80)

maybe it should be VBA.

thanks for any help!!!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Index cjjoo Excel Worksheet Functions 3 October 25th 05 09:33 AM
Returning row # using match or index of repeated text in a complex table General Excel Worksheet Functions 10 October 21st 05 03:06 PM
Match or Index Question carl Excel Worksheet Functions 2 October 4th 05 09:11 PM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 04:26 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"