match function for multiple column

[johncena]

dear friends using excel match function we can get row number in one column.for that i have use below formula.if i need to match multiple columns how i can do it.because i want to know only my selected data in which row.column doesn't mater pls help me...

Please Login or Register  to view this content.

[DonkeyOte]

You could use an Array, ie:

=MIN(IF('DYE QUEUE'!$A$1:$D$1000=L4,ROW('DYE QUEUE'!$A$1:$A$1000)))
confirmed with CTRL + SHIFT + ENTER

NOTE: when using Arrays it is imperative you keep ranges used as "lean" as possible - avoid using entire column references for ex. (indeed pre XL2007 this would generate #NUM! errors)

[ratcat]

G'day John,

Not knowing your layout and your target result. You must combine information your looking up.

Here is an example in this other thread using a VLOOKUP formula but you can also apply the idea to your MATCH formula

http://www.excelforum.com/2266317-post2.html

Cheers

RC

[johncena]

thanks u very much DonkeyOte it's work very well.now I'm facing to another problem.my first sheet D4 cell like this.
D4 cell = '458795
it is in dye queue sheet with another data.it like this.....
458795 / as-25698 / 402 / texport
pls help me.now what i can do...

[DonkeyOte]

It would be easier if you posted a sample.

Presumably you're saying you wish to conduct a partial rather than exact match, correct ?

If so and the value of interest is always to the left of the string then the test changes from:

MIN(IF(range=criteria,...))

to

MIN(IF(LEFT(range,LEN(criteria))=criteria,...))

If the value of interest can be embedded anywhere within the strings (ie not always to left) then the test becomes

MIN(IF(ISNUMBER(SEARCH(range,criteria)),...))

however in the case of the last example you may need to consider use of delimiters (and appending each string in the range) to avoid false positives.


If you need further assistance please post a sample file which reflects both setup and desired results (based on sample data).
Not doing so makes it difficult for all concerned.

[johncena]

dear DonkeyOte here i have attach my document.u can see my first sheet column "C" is empty.i need formula to that column.column "L" first 6 characters i need to match with second sheet & need to know that row number.

[DonkeyOte]

Presumably by C you meant D ?

So putting the prior post into effect (albeit with 6 char restriction - rather than LEN of L value)

=MIN(IF(LEFT('DYE QUEUE'!$A$1:$G$200,6)=L4,ROW('DYE QUEUE'!$A$1:$A$200)))
confirmed with CTRL + SHIFT + ENTER

[johncena]

ohh sorry it's column "D".thanks a lot friends.it's 200% working.additionally I'm asking possible to make macro for this.this macro should fill column "D" with above formula answers.

[DonkeyOte]

If you want the VBA to store only the results (no other formulae in your sheet) then I would suggest you adopt a different route - perhaps something along the lines of:

Please Login or Register  to view this content.

[DonkeyOte]

If you have follow up questions related to VBA please post them in a new thread - this thread has deviated somewhat from the original question.

[johncena]

YES DonkeyOte.i need only the results,i don't want other anything...

[johncena]

wow thanks u very much friend.your macro work fine.just little modification i need.i need to minus 3 from answer.because my second sheet first 3 rows are document headers.after reducing 3 that final answer should divided by 8.pls do this for me.it is very very big help for me...i have modify your formula according to my final requirement.

Please Login or Register  to view this content.