Excel 2007 : Display cell address if condition is matched

[arthurz11]

Display cell address if condition is matched

--------------------------------------------------------------------------------

For Excel 2003 or 2007: I want to display the CELL ADDRESS and not the values when a condtion is met. For example: In column A1:A10 you have positive numbers or negative numbers. The same in column B1:B10. Column A are the x coordinates and B are y coordinates.

In cell C1 I input 2 and in D1 I input -4. If the coordinates 2,-4 matches the exact coordinates of A1,B1 then I want to display the CELL ADDRESS of where those values matched not the values.

Formulas in cell E1 it will display $A$1 and in cell F1 it will display $B$1 instead of 2,-4. If it doesn't match then it displays nothing. Please help

I've tried the following but didn't work.

CELL E1:
{=IF(AND($C$1=0,$D$1=0),"",IF(SUM(($A$1:$A$10=$C$1)*($B$1:$B$10=$D$1)*1)>0,address,""))}

CELL F1:
{=IF(AND($C$1=0,$D$1=0),"",IF(SUM(($A$1:$A$10=$C$1)*($B$1:$B$10=$D$1)*1)>0,address,""))}

[ConneXionLost]

How about:

in cell E1 use:

Please Login or Register  to view this content.

and in cell F1 use:

Please Login or Register  to view this content.

Cheers,

[arthurz11]

Nope it didn't work in the formula "Column (A1),1,1) ". It does display the correct Cell Address but only if the coordinates were in Column A, in column 1, row 1. Sorry that I didn't make it clear. However, If 2,-4 was not in Cell A1, and B1 it would display the wrong Cell Address. The formula that you gave me is like entering where you already know it is.

It has to search for the x,y coordinates (in columns A and B) and when it does, it displays the Cell Address that matched the axis 2,-4. The actual spreadsheet which I am working has 200 cells in Column A and 200 cells in Column B.

So when I enter 2 in Cell C1 and enter -4 in Cell D1. In cell E1 and F1 (where the formulas should be) it will search (x,y) in Column A and Column B to see if it matches (x,y) in C1 and D1. If it does, it displays the Cell Address in E1 and F1. So if it found it on A24 and B24 then in Cell E1 it will display $A$24 and in Cell F1 is will display $B$24. Hope I didn't make it confusing. Sincerely, Arthur. Please help again.

[arthurz11]

I apologized for placing the thread on two different categories. I didn't know exactly where to place the thread. I'll make sure that I don't break rule #5 again. Sincerely, Arthur

[ConneXionLost]

It seems like you are searching for coordinate "pairs". If so, then how about:

in cell E1 use:

Please Login or Register  to view this content.

using Ctrl-Shift-Enter.

and in cell F1 use:

Please Login or Register  to view this content.

using Ctrl-Shift-Enter.

If this isn't it either, then could you attach a sample spreadsheet with sample data, and an example of what you expect the result to show?

Cheers,

[arthurz11]

Here is the sample in Excel 2010 to display a cell address if condition is matched.
Sincerely, Arthur

[ConneXionLost]

The reason my formulas didn't work is because you changed the start point of the lookup array in the MATCH function. If you leave the start point on row 1, then it would have been fine. If starting in row 1 isn't possible, then you'll need to add a correction factor to that part of the formula like this:

for cell E3, use:

Please Login or Register  to view this content.

with Ctrl-Shift-Enter


for cell F3, use:

Please Login or Register  to view this content.

with Ctrl-Shift-Enter

These formulas are now corrected for pairs that don't match the list.

Cheers,

[arthurz11]

Hi Connie!
Thank you so much for solving the Match cell address formula. Job very well done.
Sincerely,
Arthur