Retrieve data from diff sheet based on a Lookup/Index-Match over a multi-column/row range

[fletchw_111]

Hi - I've got a complicated problem I hope you can have fun with (VBA or formula solutions are welcome).

In a multi-worksheet workbook, I am trying to search a multi-column & row range in a different sheet for a cell that contains the same text as a pre-identified cell in the active sheet. If a match exists, I want to retrieve the contents from the cell to the right of the found cell.

A few wrinkles that would also help. Can the identity of the sheet to search in be variable based on the column header. Also, the search returns a match but the cell to the right is empty can the formula return a "?".

In the example below, the desired result is that ACTIVE SHEET C2 have a formula that searched the range B19:G21 of the sheet identified in C1 for the text identified in ACTIVE SHEET A2. The result would be that '1'!B19:G21 would be searched for "Jordan", which would be found in '1'!B19, so the content of '1'!C19, "x", would populate cell 'ACTIVE SHEET'!C2.

Thanks for any help (sorry this is so complicated, please write for clarification

ACTIVE SHEET

A B C D E 1 Last Name First Name 1 2 3 2 Jordan Michael 3 Olajuwuan Hakeem 4 Barkely Charles <tbody> </tbody>

Searched Sheet Title: 1

B C D E F G 18 Spares Status Spares Status Spares Status 19 Jordan x Barkeley o Colbert x 20 Smits o James x Soderbegh ? 21 Mullen Curry x Olajuwuan <tbody> </tbody>

Searched Sheet Title: 2 B C D E F G 18 Spares Status Spares Status Spares Status 19 Jones x Altuve o Jordan 20 Payton o Olajuwuan o Strawberry x 21 Fawkes Barkely o Nomo o <tbody> </tbody>

<tbody>
</tbody>

 

[Fluff]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Hi & welcome to the board.
This will put the value, rather than a formula, in the relevant cell.

Sub FindCopy()

   Dim Cl As Range
   Dim Rng As Range
   Dim Cnt As Long
   
   For Cnt = 3 To Cells(1, Columns.Count).End(xlToLeft).column
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Set Rng = Sheets(CStr(Cells(1, Cnt))).Range("B18:G21").Find(Cl.Value, , , xlWhole, , , False, , False)
         If Not Rng Is Nothing Then
            If Rng.Offset(, 1).Value = "" Then
               Cl.Offset(, Cnt - 1).Value = "?"
            Else
               Cl.Offset(, Cnt - 1).Value = Rng.Offset(, 1).Value
            End If
         End If
      Next Cl
   Next Cnt
End Sub

It will work on all cols from C onwards

 

[fletchw_111]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Wow, thanks for the response, that's really nice of you!

Unfortunately I need to clarify my question (sorry, I read the rules about putting all info in, I just screwed up).

1) Where the column used is C, it actually has to start at J (there is non-related info in columns C:I).

2) I have this macro in my VBA & am not sure how to add the new macro in (I checked this link but not sure if it's appropriate - https://www.mrexcel.com/forum/excel-questions/11487-execute-multiple-macros.html)

Thanks again & sorry for the lack of clarity at the start.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("B2")
End If
End Sub

 

[fletchw_111]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

UPDATE - testing only

FOR TESTING - I modified the sheet so that the desired column was C and ran the macros as shown below. It didn't work (Run-time (error 9): Subscript out of range.).

In my exmaple, the ACTIVE SHEET is listed in the VBA as "Sheet27 (Spares Contact Info)".

Again, sorry for the confusion.

Sub FindCopy()

Dim Cl As Range
Dim Rng As Range
Dim Cnt As Long

For Cnt = 3 To Cells(1, Columns.Count).End(xlToLeft).Column
For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Rng = Sheets(CStr(Cells(1, Cnt))).Range("B18:G21").Find(Cl.Value, , , xlWhole, , , False, , False)
If Not Rng Is Nothing Then
If Rng.Offset(, 1).Value = "" Then
Cl.Offset(, Cnt - 1).Value = "?"
Else
Cl.Offset(, Cnt - 1).Value = Rng.Offset(, 1).Value
End If
End If
Next Cl
Next Cnt
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = ActiveSheet.Range("A1")
End If
End Sub

 

[Fluff]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Ok, not a problem. This will work for col J onwards

Sub FindCopy()

   Dim Cl As Range
   Dim Rng As Range
   Dim Cnt As Long
   
   For Cnt = 10 To Cells(1, Columns.Count).End(xlToLeft).column
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Set Rng = Sheets(CStr(Cells(1, Cnt))).Range("B18:G21").Find(Cl.Value, , , xlWhole, , , False, , False)
         If Not Rng Is Nothing Then
            If Rng.Offset(, 1).Value = "" Then
               Cl.Offset(, Cnt - 1).Value = "?"
            Else
               Cl.Offset(, Cnt - 1).Value = Rng.Offset(, 1).Value
            End If
         End If
      Next Cl
   Next Cnt
End Sub

This code needs to go in a standard module, rather than a sheet module. An needs to be run manually.
The code You've supplied simply changes the sheet name to whatever you key into B2.

 

[fletchw_111]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Unfortunately, no-go.

You're exactly right about the existing code.

I used the Project-VBA Project pane to insert a module for the desired ACTIVE SHEET & pasted the code as you sent.

It ran without issue but here's the weird part, it populated cell T3 with a "?" & R15 with a "x".

R15 with "x" - the sheet identified by R1 does contain the name indicated in A15 & the "x" is correct.

T3 with "?" - the sheet identified by T1 does contain the name in A3 but the "?" is incorrect (the text in the cell to the right is "x").

 

[Fluff]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Were the other values in col T correct, or were they all wrong?

 

[fletchw_111]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Unfortunately they were empty but I have an idea. I screwed up with the example because it gave specific ranges that weren't accurate. I've tried correcting the VBA code myself but, while little changes were seen, nothing significant. (although now there are no incorrect values, just missing)

The searched range on each sheet is B18:M30.

The source for the match ranges from A2:A36.

The number of columns ranges from J:AP.

 

[Fluff]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

This accounts for the search range, the others are already dealt with

Sub FindCopy()

   Dim Cl As Range
   Dim Rng As Range
   Dim Cnt As Long
   
   For Cnt = 10 To Cells(1, Columns.Count).End(xlToLeft).column
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Set Rng = Sheets(CStr(Cells(1, Cnt))).Range("[COLOR=#ff0000]B18:M30[/COLOR]").Find(Cl.Value, , , xlWhole, , , False, , False)
         If Not Rng Is Nothing Then
            If Rng.Offset(, 1).Value = "" Then
               Cl.Offset(, Cnt - 1).Value = "?"
            Else
               Cl.Offset(, Cnt - 1).Value = Rng.Offset(, 1).Value
            End If
         End If
      Next Cl
   Next Cnt
End Sub

 

[fletchw_111]

Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Didn't work - the new code gave me the same result as my correction.

But I've identified the problem, maybe you could help.

In the ACTIVE SHEET column A contains last names & column B contains first names.

In the SEARCHED SHEET, the cells being searched contain first & last names. So the search has to be a CONTAIN rather than an exact match. Is this possible? I'm guessing it's a mod to the For Each CL In Range line but I'm not sure how to express that properly.

Sorry for that.

Also - is there a way to have this automatically run all the time so that the ACTIVE SHEET is updated as changes are made to the SEARCHED SHEETS?

Thanks a million, I can't believe your help & patience!