• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding matching qty from a group

C

Chetan

New Member
Hi All,


I have an excel file wherein i have a rate card for the paper qty of different groups.

The Rates available with me are for group of qty like, 0-100, 101-200 etc.

The Rates are different for each group.


Can any one please help me with deriving the exact rate from the group of qty.?


I have attached the sample xls file for reference.


https://docs.google.com/file/d/0B4qS8udSe7loemdRYWxhVXc5M0E/edit?usp=sharing


Thanks,

Chetan
 
Hi Chetan,


If you will not be looking below 100, this should work:


Code: 
=LOOKUP(D7,D3:M3,D4:M4)


Faseeh
 
Hi,


The formula given by you is not working as per the requirements.

Refering to my xls attached, if i enters say 400 (in D7) the value it should return in D8, is 0.30 as the 400 is more than 200 and less than 500.


Cheers !


Chetan
 
Hi Narayan,


the formula entered by you is what i was looking for.


It is working exactly as per my requirement.


Thanks a tone !!


Thanks,

Chetan
 
NARAYANK991 said:
Hi Chetan ,


Try this , entered as an array formula , using CTRL SHIFT ENTER :


=IFERROR(SMALL(IF($D$3:$M$3>=$D$7,$D$4:$M$4),1),MAX($D$4:$M$4))


Narayan
Click to expand...

Hi Narayan,

I did tried implementing your formula in my excel file, it is not working as per the requirement, dont know how.

i have attached my xls file. Please help me.

Thanks,
Chetan
 

Attachments

  • Sample.xlsx
    13 KB · Views: 7
Pretty Perfect. !! Kudos :)

What if i have to use the range D4:N4.

I guess you have worked on D3:N3 range.

Cheers !!
 
Chetan said:
Pretty Perfect. !! Kudos :)

What if i have to use the range D4:N4.

I guess you have worked on D3:N3 range.

Cheers !!
Click to expand...

Maybe......

=LOOKUP(D8,SUBSTITUTE(C4:M4,C4,-1)+1,D5:N5)

And,

If you insist to use the range D4:N4, something like :

=IFERROR(LOOKUP(D8,SUBSTITUTE(D4:N4,N4,-1)+1,D5:N5)+0.1,D5)

Regards
 
Last edited:
Thanks @Chetan. But in this case the lookup array is sorted in ascending order. Hence match type 1 (MATCH(D7,D3:M3,1)) . For descending order it would be -1
 
You must log in or register to reply here.
Back
Top