reverse look up using vba macro (beginner)

[alksndr]

Hi,

would like to know how can I execute reverse look up or index match using vba macro?

My worksheet looks like this:

Row      Column A       Column B       Column C       
  1        Process          Subprocess     Ref. Indicator
  2        Reporting       Statutory        FO/RM
  3        Reporting       Projects          FO/RP
  4        Reporting       Projects          FO/RPW
  5        Reporting       Projects          FO/RPB

So my look up value is in column C while my return value is in column A


Thank you guys for the help!

 

[wideboydixon]

Since the data to return is to the left of the data you're looking up, you need to use INDEX/MATCH:

Book1
	A	B	C	D	E	F
1	Process	Subprocess	Ref. Indicator		Lookup Ref Value:	FO/RPW
2	Reporting	Statutory	FO/RM		Process:	Reporting
3	Reporting	Projects	FO/RP
4	Reporting	Projects	FO/RPW
5	Reporting	Projects	FO/RPB
Sheet1

Cell Formulas
Range	Formula
F2	=INDEX($A$2:$A$5, MATCH($F$1, $C$2:$C$5, 0))

WBD

 

[alksndr]

Apology but I forgot to include this condition >> after determining the Process, macro should insert another column beside the lookup value

example:

Sheet1

                       *lookupvalue    *insert
                                       returnvalue
Row      Column A       Column B       Column C       Column D       
  1      Emp ID         Ref Indicator  Process        MLA-DEL
  2      PHMABI         FO/RM          Reporting      MLA-DEL
  3      PHMABC         FO/RP          Reporting      MLA-DEL
  4      PHACBV         FO/RPW	       Reporting      MLA-DEL

Sheet 2

Row      Column A       Column B       Column C        
  1      Process        Subprocess     Ref. Indicator 
  2      Reporting      Statutory      FO/RM 
  3      Reporting      Projects       FO/RP 
  4      Reporting      Projects       FO/RPW 
  5      Reporting      Projects       FO/RPB

Hi,

would like to know how can I execute reverse look up or index match using vba macro?

My worksheet looks like this:

Row      Column A       Column B       Column C       
  1        Process          Subprocess     Ref. Indicator
  2        Reporting       Statutory        FO/RM
  3        Reporting       Projects          FO/RP
  4        Reporting       Projects          FO/RPW
  5        Reporting       Projects          FO/RPB

So my look up value is in column C while my return value is in column A


Thank you guys for the help!

 

[wideboydixon]

C2 formula in Sheet 1:

=INDEX(Sheet2!$A$2:$A$5, MATCH($B2, Sheet2!$C$2:$C$5, 0))

Then copy the formula down.

WBD

 

[alksndr]

Hi WBD,

Thank you for the quick reply!

But I'm hoping that I could do this with macro so that I wouldn't have to do it manually. any idea would be great. thanks!

C2 formula in Sheet 1:

=INDEX(Sheet2!$A$2:$A$5, MATCH($B2, Sheet2!$C$2:$C$5, 0))

Then copy the formula down.

WBD

 

[wideboydixon]

Public Sub ReverseLookup()

Dim lastRow As Long
Dim rowLoop As Long

lastRow = Sheets(1).Range("A" & Sheets(1).Rows.Count).End(xlUp).Row
If Sheets(1).Range("C1").Value <> "Process" Then
    Sheets(1).Columns(3).Insert
    Sheets(1).Range("C1").Value = "Process"
End If

Sheets(1).Cells(2, 3).Resize(lastRow - 1).Formula = "=INDEX('" & Sheets(2).Name & "'!A:A, MATCH(B2, '" & Sheets(2).Name & "'!C:C, 0))"

End Sub

WBD