identifying a cell in a range from its value

[megame]

Hello everyone!

I am not sure how to do the following on Excel 2007:
- I am considering 2 series of consecutive cells organised in 2 lines (same number of cells, one a few lines above the other).
these lines are comprised of rankings for a product A (top line) and a product B (bottom line). The columns are the months of the year.
In any given column of the range we're considering,, in the cells where the column crosses product A's line and product B's line, there can be either the number zero, or nothing, or a rank that is unique for both series : there is only one number 4, for instance, in all the cells of product A's series and product B's series; and the same for all other number.

What I'd like to do is the following: starting from a given rank (say 5)that I know is present somewhere in a cell of one of the two series, I'd like a formula that would examine both series, find the cell where the content is equal to the chosen rank (5), and identifies it.i.e. gives L15C63 as the result, being the absolute designation of the cell of which the value equals 5.

I hope I'm being clear. Can you help me? It's quite urgent.

Thanks!

[darkyam]

Perhaps a dummy workbook would help. I confess that I can't quite visualize what it is you're trying to do.

[megame]

OK, I'm preparing a workbook and will be done in 5 mins, bear with me!
Thanks

[megame]

Here it is:

[DonkeyOte]

I think it's fairly apparent to you by now that the setup isn't ideal from an analysis perspective... given your setup you would (IMO) be better off returning the address as a co-ordinate, eg:

D23:
=MIN(IF(($A$10:$A$14="RANK")*($B$10:$AZ$14=$C23);ROW($B$10:$B$14)+(COLUMN($B$10:$AZ$10)/100000)))
confirmed with CTRL + SHIFT + ENTER
copied down
(adjust ranges to suit)

The above would for ex. return 10.00002, 14.00002, 14.00003, 10.0004 etc...

You can use the above values in an INDEX call to retrieve other values based on this "locator" given the Integer represents the ROW and the decimal remainder the column - eg:

=INDEX($6:$6;ROUND(MOD($D23;1)*100000;0))

would return the associated date

=INDEX($A:$A;INT($D23)-3)

would return the associated product

=INDEX($1:$14;INT($D23)-1;ROUND(MOD($D23;1)*100000;0))

would return the cumulative for the product etc...

(note the final INDEX uses both row_num and col_num parameters whereas the former do not given they are referencing vectors (ie single row, single column range) rather than matrices)


Hopefully the above gives you sufficient info. to extend this further per your own requirements.

[megame]

Thanks a lot, and congrats for a clever solution I would never have thought of. Carrying the two coordinates of a cell in one number..;well done.

That being said, I did not manage to have the formula working (error message #NAME? ). I may be doing something wrong...
I attached the dummy workbook with the formula in D23 so you can see.

Would you mind taking a look?

Many thanks in advance

[DonkeyOte]

Works for me I'm afraid - you may need to translate per your own version if non-english functions - are you using French, Dutch, German ?

French | German |Dutch

ROW: LIGNE | ZEILE | RIJ
COLUMN: COLONNE | SPALTE | KOLOM

MIN: all same as English

[megame]

You're right, I'm so used to working in both languages that I didn't pay attention to that.
Once translated, it works.
Now I will try the rest and will let you know.
Again, many thanks.

[megame]

Alright, everything works as intended: you saved me.

This topic can be marked as Solved.

Thanks a lot!

Enjoy your day