Cell reference from 2 Lookups

[Guest]

Newbie

1. I would like to find a matching number in column C. I want the function
to return what row it was found in.

2. I would also like to find a matching number in row 3. I want the
function to return what column it was found in.

3. I then want to place a number in intersecting row and column found from
#1 and #2 above.

Let me give an example:

A B C D E F G
1 4
2 8
34 8 9 10 1 2 3
4 10
5 13
6 9 (N)

Let's say I want excel to search for the number 1 in row 3. It should
return the column it was found in which is E.

Let' say I want excel to search column C for the number 9. It should return
the row it was found in which is 6.

Then I want excel to enter another number let's call it (N) in the column
and row it found earlier which is E6.

Please help.

 

[Guest]

If you want Excel to automatically (as opposed to you entering it manually)
enter N, where is N stored? Presumably N is in a table or can be calculated?

 

[Guest]

I'm sorry. N would be stored in A1.

 

[Guest]

This simply code would do it but how would you want it triggered? and where
are the co-ordinates placed? In my example they are in H1 (1) & H2 (9).

In your example 9 appears twice in column C: my test assumes onlly a single
occurence of a digit.

Sub Insert_N()
With Application
icol = .Match(Range("h1"), Range("3:3"), 0)
irow = .Match(Range("h2"), Range("C:C"), 0)
End With
Cells(irow, icol) = Range("a1")
End Sub

 

[Guest]

Sorry about two different nines, actually there would be no duplicate numbers
so don't worry about that.

On your other question, I don't anticipate needing the coordinates again --
at least for now. I just want (N) placed at the proper co-ordinates.

Could it be triggered whenever a new number was placed at A1?

That leads to my next question. How and where would I place the function
you created? I believe in A1. Am I right?

 

[Guest]

YES, It could be triggered by a change to A1. Code is below. Right click on
tab of your w/sheet, "View Code", and copy and paste code.

IT STILL requires you put the co-ordinates in H1 & H2 but these can be
changed to suit.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_Exit:

If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False

With Application
icol = .Match(Range("h1"), Range("3:3"), 0) '<=== change h1 if required
irow = .Match(Range("h2"), Range("C:C"), 0) '<=== change h2 if required
End With
Cells(irow, icol) = Range("a1")
ws_Exit:
Application.EnableEvents = True
End Sub

 

[Guest]

Hi

So far so good, thanks so much. Wow!