Cell reference from 2 Lookups

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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?
 
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
 
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?
 
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
 
Back
Top