• 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.

Match and index formula help required

J

Jagadeeshbs

Member
Hello all


I have date like

Cash in Banks - Oper. Accts. 100000 - 100099

Cash in Banks - Time Deposits 101000 - 101099

Restrictive Cash - Current 102000 - 102099

Marketable Securities - Trading 103000 - 103099

Marketable Securities - Available for Sale 104000 - 104099

Marketable Securities - Held to Maturity 105000 - 105099

Marketable Securities - Partnerships 105500 - 105599


In cell A1 I have data like 101009

A2 105060

I need a formula to which should give data for me is like this :

A B

101009 Cash in Banks - Time Deposits

105060 Marketable Securities - Held to Maturity


I guess this can be done through match and index but I am not able to get it..

Please help me with this regard.

Thanks

Jagadeesh
 
Assuming data is in A2:C8 = 3 columns

Cash in Banks - Oper. Accts. 100000 100099

like

[pre]
Code: 
Cash in Banks - Time Deposits 	 	 	101000	101099
Restrictive Cash - Current 	 	 	102000	102099
Marketable Securities - Trading 	 	103000	103099
Marketable Securities - Available for Sale 	104000	104099
Marketable Securities - Held to Maturity 	105000	105099
Marketable Securities - Partnerships 	 	105500	105599
[/pre]
then if in

A20 you have 101009

A21 you have 105060


In B20: =OFFSET($A$1,SUMPRODUCT(($B$2:$B$8<=A20)*($C$2:$C$8>=A20),ROW($B$2:$B$8)),0)

adjust to suit
 
Jag,


=INDEX(FromWhichColumnYouWantData,  MATCH(WhatAreYourSearchingFor,  WhichColumnNeedsToBeSearched,  FALSE))


Try this simple explanation and let us know if you were able to get the output as desired.


~Vijay
 
Dear Vijay


i came to know that this match and index will not help in this criteria.


Hui formula helps but somewhere its missing in picking the data...

i guess its coz of reference cells given wrong...


waiting for the reply form him...:)
 
Do you have 3 columns of data?

Which Columns/rows is your data in ?
 
Hui


I am not getting my desired results...can u give me ur mail id so i can send you the file.

My mail id is Jaggabs@yahoo.co.in
 
Jag,


Hui's formula is absolutely rocking...


I have adjusted the same as per the sheet that you sent across.


Use this formula in the Yello cells to get the output (however this is not 100% correct)


=OFFSET(Sheet1!$C$1,SUMPRODUCT((Sheet1!$F$1:$F$542<=E2)*(Sheet1!$G$1:$G$542>=E2),ROW(Sheet1!$C$1:$C$542)),0)


Things to remember...

1) when using SUMPRODUCT... you need to define the range....

You cannot work on F:F or A:A

2) You will get 0 as output as your search values are mixed (number and alphanumeric)


Finally you need something else... as this formula will not get you the desired results even though there is nothing wrong with it.


@Hui..

The search Columns contains number as well as 1930FH for example...


~Vijay
 
You must log in or register to reply here.
Back
Top