How to nest formula MATCH , ROW to display multiples criteria

[Franky alta]

Hi,

Note: using a table and lookup for sure i do the job, but i can't use a table because there are other purpose ahead.

what i'm doing:
i'm using excel helper to find row location of cell "I1" content in range A3:A20, The formula work perfect!

Please Login or Register  to view this content.

for example:
Cell 'I1' have #100 as content, and in Range A3:A20 Is a number #100 located in row 18, then (where formula is copied) i got 18

What i need to do:
But now i would like to add more content to located row position of 'I1', 'K1', 'O1, 'P1', 'R1' in same Range A3:A20
How can i do that?, perhaps i tried

Please Login or Register  to view this content.

and as return on first Row it display -52 (should display 0 )

So that's why of my question : How to nest the formula?


Thank you!

[bebo021999]

Try:
=SUMPRODUCT(--(($A$3:$A$20=$I$1)+($A$3:$A$20=$K$1)+($A$3:$A$20=$O$1)+($A$3:$A$20=$P$1)+($A$3:$A$20=$R$1))*ROW($A$3:$A$20))

[Franky alta]

Hi bebo02199,

thanks for fast response.
unfortunately the formula is not working.
It display row 27 (content of cell 'I1') on row where is supposed to show 0, then when i copy the formula down it still show 27 on entire range.

Franky

[FDibbins]

I think you need to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like. I am having a hard time visualising what you have

[Franky alta]

Hi FDIbbins,

You're right, i thought the formula was no to hard to figure it out..


please find sample file attached.

[FDibbins]

So what would a sample answer look like?

You can find the row number by just using MATCH...
=MATCH($I$2,A:A,0)

[Franky alta]

Hi Mr FDibbins,

As

Please Login or Register  to view this content.

it work but only for I2, (as the other formula does)

Please Login or Register  to view this content.

But what i need to know is How do i nest any of these formula to include 'I2', 'K2', 'O2, 'P2', 'R2' (not only I2) to search and display (in Column U )the Row number of string
of Range A3:A20?


thanks

[FDibbins]

Perhaps you missed this part?

So what would a sample answer look like?

[Franky alta]

Hi FDibbins,

The sample look like :

Excel Helper Columns
Result___Formula
Num___ MATCH/ROW
50________ 0
35________ 0
06________ 0
44________ 6 <--- #44 located on Row 6
07________ 0
88________ 8 <--- #88 located on Row 8
27________ 0
72________ 10 <--- #72 located on Row 10
22________ 0
79________ 0
23________ 0
85________ 0
80________ 15 <--- #88 located on Row 15
90________ 0
99________ 0
100_______ 18 <--- #100 located on Row 18
77________ 0
70________ 0

*No arrow/Text included

Thanks!!

[FDibbins]

yes, I can see that in your file. How do you arrive at that. Which part are you entering and which part are you trying to calc?
What is =INDIRECT("$A$3") supposed to be doing? That could just as easily have been =A3

TBH, I have no clue what you are trying to do here, so be gentle with me, explain what you are doing

[Franky alta]

Hi FDibbins,

be gentle with me... lol

where INDIRECT is a excel helper column,is just to indicated the row number belong to which number
Column T have the same values of Column A trough INDIRECT.

i know it can be easy to place A3 (than INDIRECT) But if you noticed, Column B have some values sorted most to least that belong to each cell of Column A,
so both column A and B are filtered. that's why i used INDIRECT on column T when string change position up or down.

thanks !

[FDibbins]

You stil have not explained what you want and how you would do it?

[Franky alta]

Hi,

I need to return the Row position of string searched (cell I2, K2, O2, P2 and R2) on Column U (next to corresponding Number)

[FDibbins]

Try this, no helpe column used...
=IFERROR(MATCH(INDEX($I$2:$R$2,MATCH(A3,$I$2:$R$2,0)),A:A,0),"")
copied down

[Franky alta]

Hi FDibbins,

Yes, it work!!!

Thank you !!!!!!!!!!!!!!

[FDibbins]

Im happy we got you where you wanted to be, thanks for the feedback