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

Cell Name at prefix

mukesh bajaj

New Member
Hey, I have a little problem. I used the formula ,Match(), where I used Match(a1,Match():Match(),0) where I got 55:105 values form inside match functions match():match(). Now to make my formula work, I need to add column name before those (55:105) means a55:a105. I used "A"&match():"A"&match() but got the anser "a55":"a105" instead of a55:a105(means got extra inverted commas). also used concatenate but in vain. Kindly tell me.
 
I'd recommend uploading sample workbook.

But from the sound of it. You just need INDEX().
Ex:
=INDEX(A:A,MATCH()):INDEX(A:A,MATCH())
 
That didnt work. Now see the attached file. My problem is, I want to draw the Rate of Firm using 3 Variables(Name of work"Creation of Room", Name of Company "JKL", Rate of Item"Sand"). I used the formula that checks first two variables and if those two matches, I want to draw the rate of sand which that firm is offering. (Use f9 for cheking the result of each formula that I applied, you must know. just in case.) Reply in simplest form(just the formula,as you did in =INDEX(A:A,MATCH()):INDEX(A:A,MATCH()) ) also put the values in next row using the same formula.
 

Attachments

  • question file (2).xlsx
    22.1 KB · Views: 12
Hmm, you fell into common trap. Using form like structure as starting point.

While the layout may be more human readable, and easier for data entry. It is absolutely horrid for data analysis and calculation.

Always store data in flat table structure for ease of analysis.

If you want to use form for data entry, then create Userform with bit of VBA. Trust me, it will make your life much easier.

Not sure what you are trying to accomplish from your sample. I'd suggest detailing what your end goal is and also give few manually derived examples of desired result.
 
Hey Bosco_yip, Actually, we both work together and our superior gave this task to solve. I have to submit the solution for this in different way than to his. So if you could possibley solve this problem in my way as I am quite closer to get the result or suggest a way different to what you suggested to paramveer, the other guy. Thing is, I just want to solve a problem with there variations(which are repeating but not simultaneously), if all the 3 variable comes together, then take the result of that 3rd variable in its third column. which is what my problem is. Also None of any gave the solution to paramveer too in the link that you suggested.
 
Last edited:
mukesh bajaj
If paramveer would had given more details then he would get more replies.
If only waiting ... then it's challenge to give more without new detail - information - results and so on.

You confirm that this is like same thread - it mean like cross-posted thread.
Have You read
 
That didn't help. Yes, I read it now Ok I will make this thread different. I want to make a range from match formula. eg: there is a Name written "MKS" in the row 55. and another Name "MKS2"in row 65. I use match formula which find "mks" and "mks1" in their respective rows which is 55 and 65. but use these name for cell reference for another formula like "Index(reference,match():match(),0)" and I it give me result "Index(reference,55:65,0)" but i want "Index(reference,A55:A65,0)". Kindly help
 
Last edited:
mukesh bajaj
You seems to clear Your previous original reply ... this seems different than I got.
It's good that You start [re]reading 'Site Rules'
... then You know what should You take care eg with cross-posting.
There are many good hints How to get the Best Results at Chandoo.org.
... waiting isn't the best way ... to be patient would be better.

Your want...

Match gives a number as a result ... as You have gotten - so that's is correct.
Also Your #1 formula "A"&match():"A"&match() but got the anser "a55":"a105" looks correct result.
Have You tried "A" & match() & ":A" & match() combination would give You needed string ( Anumber1:Anumber2 ),
but ... how useful would it be ... that would be other case ... but it would be that You're looking for.

Ps. If You refer something with sheets ... it would be better to upload an Excel-file, which has same data with needed results.
 
that worked but when I use it alone. when i use it in index formula then it again gives "a55":"a105". Although you helped me in this, so to solve this, i was looking for something else on youtube but in that problem, they used indirect() formula. This is what we want now. Indirect() formula converts any number to cell reference.
 
mukesh bajaj
As I wrote: but ... how useful would it be ... that would be other case ... but it would be that You're looking for.

Have You tried to search information of INDIRECT-function?
eg: There seems to be suitable samples for You.
 
Yes, searched many examples and got to know about indirect() fn. converting text to reference, which is what i want 55:105 to A55:A105.
 
Back
Top