Cell Reference from 2 lookups

[joesf16]

Newbie

I have written in about this before, but I have become lost.
I am hoping if I try again I can get back on track.

Using Office 2003


I need this done with formulas, no vba or macros.
I have three numbers one in B1, C1, and D1.
There are no duplicate numbers.


1. I want excel to search for the number in B2 in Row 3. I want it to
place the column number where it found the number in H1.

2. I want excel to search for the number in C1 in Column C. I want it to
place the row number where it found the number in H1.

3. I then want the number in D1 placed in the cell referenced in H1.



I have created a spreadsheet below to help explain what I
am trying to do.

1. You see I have three numbers in B1,C1, and D1.
2. Excel locates the number in B1 in row 3. It returns $F to H1.
3. Excel locates the number in C1 in column C. It returns row $7 to H1.
4. Excel takes the number in D1 and places it in the cell coordinates $F$7
produced in H1.


A B C D E F G H I J
1 11 3 90 $F$7
2
3 2 14 15 11 21 33 18 36
4 7
5 9
6 1
7 3 90
8 4

Excel people have offered the following formula which can be placed in D4 to
J8:

=IF(CELL("Address",D4)=$H$1,$A$1,"")

This might need to be slightly changed because I have changed what I was
trying to do initially somewhat.





vze2mss6

 

[JLatham]

In H1 I put this formula:
=ADDRESS(MATCH($C$1,$C$3:$C$65536,0)+2,MATCH($B$1,$3:$3,0))
that just shows the address developed, if any, and doesn't have anything to
do with displaying the value within the grid.

I then filled all of the cells within the grid (D4:J8) with this formula:

=IF(AND(ROW()=MATCH($C$1,$C$3:$C$65536,0)+2,COLUMN()=MATCH($B$1,$3:$3,0)),$D$1,"")

Seems to work for me as long as there are matches to the cells in B1 and C1.
Otherwise you get a bunch of #NA indications. That can be fixed by wrapping
the formulas in a test for NA setup:
for H1:
=IF(ISNA(ADDRESS(MATCH($C$1,$C$3:$C$65536,0)+2,MATCH($B$1,$3:$3,0))),"",ADDRESS(MATCH($C$1,$C$3:$C$65536,0)+2,MATCH($B$1,$3:$3,0)))

and for the cells inside of the grid area:
=IF(ISNA(IF(AND(ROW()=MATCH($C$1,$C$3:$C$65536,0)+2,COLUMN()=MATCH($B$1,$3:$3,0)),$D$1,"")),"",IF(AND(ROW()=MATCH($C$1,$C$3:$C$65536,0)+2,COLUMN()=MATCH($B$1,$3:$3,0)),$D$1,""))

Hope that helps some.

 

[T. Valko]

I replied to your other post. Did you try what I suggested? I combined the
formulas and eliminated the need for the formula in H1. But, if you want
that one:

Enter this formula in H1:

=IF(COUNTIF(C3:J3,B1)+COUNTIF(C3:C8,C1)<2,"",ADDRESS(MATCH(C1,C3:C8,0)+2,MATCH(B1,C3:J3,0)+2))

Enter this formula in D4:

=IF(OR($D$1="",$H$1=""),"",IF(CELL("Address",D4)=$H$1,$D$1,""))

Copy across to J4 then down to row 8.

Biff

 

[JLatham]

Nice. I couldn't see a need for the address in H1 either, the stuff I did
above doesn't require it, I just set it up to show something in that cell. I
like your solution for the body of the table/matrix better than mine - much
shorter to type out!

 

[T. Valko]

CELL is volatile but the range is small, so.....it shouldn't hurt anything.

There seems to be a flaw in the table, though.

2 14 15
7
9

Seems to me that 2 should be 1 column to the right:

.....2 14 15
7
9

Biff

 

[JLatham]

I thought same thing about the table - needs empty upper left corner. But
for demonstration purposes (unless you put 2 in as one of the values) it
suffices.

Just goes to show you different interpretations: I thought perhaps the 2, 7
9 series should be pushed down 1 row.

 

[joesf16]

Thanks ! It works ! Wow!

I have a couple of more questions for you.

* When I went to copy and paste the formula into D4 to J8, I first dragged
the handle across to D8 then I released it. This copied the formula across.

* Then I dragged the handle down from D8 to J8 and this seemed to copy the
formula down.

1. Am I correct? It is actually two separate procedures. First you drag
across and then you have to drag down?


* You have a point I should clear the digit in the upper left corner. I
see what you mean that that could cause a problem. Consider the upper left
cell in my table empty.


2. I would like the numbers in column C to be unique nine digit numbers.
Does that change what we need to do with your formulas at all?

3. I would like the numbers in row 3 to be 1 or 2 digit numbers. Does that
change what we need to do with your formulas at all?

4. The number in D1 may be the same as the number in row 3. Is that
possible to do?

Thanks so much

 

[JLatham]

The way you used the handle to fill the formula across and down is fine.
Could have done it in reverse sequence also (down then across).
Considering the 'clean up' of the table values I don't see any problem with
any of the other questions you asked. The formula is simply matching numeric
values - looking for exact matches. It doesn't care if it is 1 digit or 15.
The number in D1 is pretty much independent of the formula, it is something
just being copied or referenced.

The only part of the formula that might need adjusting would be the value (
+2) used in it with reference to matching in column C. We have to start
below row 1 where you have a value in C1. You see that the the area examined
is from C3 down to the bottom of that column on a pre-2007 worksheet. The +2
gives us the correct row number of where the match is found. So if your
table started lower, say 1 more row down and we looked from C4 on down, then
change the +2 to +3 and it still works.

Ignore this if you don't understand it or it confuses you - the formula
already works, ok? But if you know the exact range for the two series of
numbers, you could change the formula to just look in that range for improved
performance. Let's say your vertical series goes from C3 to C33 and the
horizontal series goes from D3 over to AB3. Then the 'meat' of the formula
could look like this
=IF(AND(ROW()=MATCH($C$1,$C$3:$C$33,0)+2,COLUMN()=MATCH($B$1,$D$3:$AB$3,0)),$D$1,"")

 

[T. Valko]

Here's a sample file:

joesf16.xls 14kb

http://cjoint.com/?fyeAo71E8B

I used a slightly better formula in the table (dropped the volatile CELL).

Half of the ADDRESS formula in cell H1 is dedicated to error checking.

Biff

 

[joesf16]

Many thanks to you T. Valko & JLatham. It is working.

A few more questions though.

1. My number in D1 may in a few cases be the same as
the number in B1. Any problems with that?

2. Is there a way I can lock these formulas in
so that they cannot be tampered with or accidentally
changed?

3. Is it possible to hide them as well?

 

[T. Valko]

My number in D1 may in a few cases be the same as

the number in B1. Any problems with that?

No, none at all.

Is there a way I can lock these formulas in so that they
cannot be tampered with or accidentally changed?
Is it possible to hide them as well?

Yes. Set sheet protection. The exact options available to you depend on what
version of Excel you're using. You can at least set these basic options:

Select the *entire* sheet by clicking on that little square to the left of
column A and above row 1.
Goto the menu Format>Cells>Protection tab
Uncheck Locked
OK out

Now, select cell H1 and the range of formula cells in the table.
Goto the menu Format>Cells>Protection tab
Check *both* Locked and Hidden
Ok out

Now, set the sheet protection
Goto the menu Tools>Protection>Protect sheet
Select the options you want
Ok out

Biff