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

F

fletchw_111

New Member
Joined
Aug 18, 2015
Messages
9
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
AB
Last NameFirst Name
JordanMichael
OlajuwuanHakeem
BarkelyCharles

<tbody>
[TD="class: xl71"]ACTIVE SHEET
[/TD]

[TD="class: xl71"][TABLE="width: 417"]
<tbody>[TR]

[TD="align: right"]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]

[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD="class: xl71"]Searched Sheet Title: 1
[/TD]
[/TR]
[TR]
[TD="class: xl71"]
18

<tbody>
[TD="align: center"][/TD]
[TD="class: xl67, width: 101, align: center"]B[/TD]
[TD="width: 42, align: center"]C[/TD]
[TD="class: xl67, width: 101, align: center"]D[/TD]
[TD="width: 42, align: center"]E[/TD]
[TD="class: xl67, width: 101, align: center"]F[/TD]
[TD="width: 42, align: center"]G[/TD]

[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]
[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]
[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]

[TD="class: xl66"]19[/TD]
[TD="class: xl66"]Jordan[/TD]
[TD="class: xl66"]x[/TD]
[TD="class: xl66"]Barkeley[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]Colbert[/TD]
[TD="class: xl66"]x[/TD]

[TD="class: xl66"]20[/TD]
[TD="class: xl66"]Smits[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]James[/TD]
[TD="class: xl66"]x[/TD]
[TD="class: xl66"]Soderbegh[/TD]
[TD="class: xl66"]?[/TD]

[TD="class: xl66"]21[/TD]
[TD="class: xl66"]Mullen[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Curry[/TD]
[TD="class: xl66"]x[/TD]
[TD="class: xl66"]Olajuwuan[/TD]
[TD="class: xl66"][/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[/TR]
[TR]
[TD="class: xl71"]Searched Sheet Title: 2
18

<tbody>
[TD="align: center"][/TD]
[TD="class: xl67, width: 101, align: center"]B[/TD]
[TD="width: 42, align: center"]C[/TD]
[TD="class: xl67, width: 101, align: center"]D[/TD]
[TD="width: 42, align: center"]E[/TD]
[TD="class: xl67, width: 101, align: center"]F[/TD]
[TD="width: 42, align: center"]G[/TD]

[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]
[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]
[TD="class: xl65, align: center"]Spares[/TD]
[TD="class: xl68, align: center"]Status[/TD]

[TD="class: xl66"]19[/TD]
[TD="class: xl66"]Jones[/TD]
[TD="class: xl66"]x[/TD]
[TD="class: xl66"]Altuve[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]Jordan[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]20[/TD]
[TD="class: xl66"]Payton[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]Olajuwuan[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]Strawberry[/TD]
[TD="class: xl66"]x[/TD]

[TD="class: xl66"]21[/TD]
[TD="class: xl66"]Fawkes[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]Barkely[/TD]
[TD="class: xl66"]o[/TD]
[TD="class: xl66"]Nomo[/TD]
[TD="class: xl66"]o[/TD]

</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Could you please show an accurate sample of the data on both the active sheet & one of the sheets to be searched?
Cheers
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Hi Fluff,

I wanted to thank you & update you on some progress. I had some help from some family & they made a tiny mod to your code & it fixed the problem (see below).

Now a new problem (cut one head & 2 more sprout :P). How to add a section that instructs the code to return a blank space (" ") if the name is not found in the search range? This is to overwrite a cell's contents if a name is deleted from the searched range after the macro was run.

Thanks a ton for all of your help, I've been telling everyone how awesome this message board is & how helpful it is, just amazing!

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, , , xlPart, , , 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
 
Upvote 0
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

If I've understood you correctly, this will replace the name in the active sheet with a space.
Code: 
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:M30").Find(Cl.Value, , , xlPart, , , 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
         Else
            Cl.Value = " "
         End If
      Next Cl
   Next Cnt
End Sub
 
Upvote 0
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Unfortunately no. The problem scenario is: the macro is run & the ACTIVE SHEET is populated. Then a name is deleted from one of the SEARCHED SHEETS & the macro is run again. The ACTIVE SHEET remains populated even though the name is no longer in the SEARCHED RANGE. (example below - the red x should be replaced by a blank space).

I think the answer is including an argument that IF the content of A2 does not xlPart match any cell in the range, return " ", but I don't know if that's right or how to do that :P

Thanks again!

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl71"]Searched Sheet Title: 1 - before macro run
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"] [TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]B[/TD]
[TD="width: 42, align: center"]C[/TD]
[TD="class: cms_table_xl67, width: 101, align: center"]D[/TD]
[TD="width: 42, align: center"]E[/TD]
[TD="class: cms_table_xl67, width: 101, align: center"]F[/TD]
[TD="width: 42, align: center"]G[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]19[/TD]
[TD="class: cms_table_xl66"]Jordan[/TD]
[TD="class: cms_table_xl66"]x
[/TD]
[TD="class: cms_table_xl66"]Barkeley[/TD]
[TD="class: cms_table_xl66"]o[/TD]
[TD="class: cms_table_xl66"]Colbert[/TD]
[TD="class: cms_table_xl66"]x
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]20
[/TD]
[TD="class: cms_table_xl66"]Smits[/TD]
[TD="class: cms_table_xl66"]o[/TD]
[TD="class: cms_table_xl66"]James[/TD]
[TD="class: cms_table_xl66"]x[/TD]
[TD="class: cms_table_xl66"]Soderbegh[/TD]
[TD="class: cms_table_xl66"]?[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]21
[/TD]
[TD="class: cms_table_xl66"]Mullen[/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"]Curry[/TD]
[TD="class: cms_table_xl66"]x[/TD]
[TD="class: cms_table_xl66"]Olajuwuan
[/TD]
[TD="class: cms_table_xl66"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl71"]ACTIVE SHEET - after macro run once
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"][TABLE="class: cms_table, width: 417"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD="align: right"]J
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Last Name
[/TD]
[TD]First Name
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Jordan[/TD]
[TD]Michael[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Olajuwuan[/TD]
[TD]Hakeem[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Barkely[/TD]
[TD]Charles[/TD]
[TD]o
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl71"]Searched Sheet Title: 1 - before macro run a second time
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"] [TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="class: cms_table_xl67, align: center"]B[/TD]
[TD="width: 42, align: center"]C[/TD]
[TD="class: cms_table_xl67, width: 101, align: center"]D[/TD]
[TD="width: 42, align: center"]E[/TD]
[TD="class: cms_table_xl67, width: 101, align: center"]F[/TD]
[TD="width: 42, align: center"]G[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[TD="class: cms_table_xl65, align: center"]Spares[/TD]
[TD="class: cms_table_xl68, align: center"]Status[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]19[/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"]Barkeley[/TD]
[TD="class: cms_table_xl66"]o[/TD]
[TD="class: cms_table_xl66"]Colbert[/TD]
[TD="class: cms_table_xl66"]x
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]20
[/TD]
[TD="class: cms_table_xl66"]Smits[/TD]
[TD="class: cms_table_xl66"]o[/TD]
[TD="class: cms_table_xl66"]James[/TD]
[TD="class: cms_table_xl66"]x[/TD]
[TD="class: cms_table_xl66"]Soderbegh[/TD]
[TD="class: cms_table_xl66"]?
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl66"]21
[/TD]
[TD="class: cms_table_xl66"]Mullen[/TD]
[TD="class: cms_table_xl66"][/TD]
[TD="class: cms_table_xl66"]Curry[/TD]
[TD="class: cms_table_xl66"]x[/TD]
[TD="class: cms_table_xl66"]Olajuwuan
[/TD]
[TD="class: cms_table_xl66"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl71"]ACTIVE SHEET - after macro run a second time
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD="align: right"]J
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Last Name
[/TD]
[TD]First Name
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Jordan[/TD]
[TD]Michael[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Olajuwuan[/TD]
[TD]Hakeem[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Barkely[/TD]
[TD]Charles[/TD]
[TD]o
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

Ok, how about
Code: 
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:M30").Find(Cl.Value, , , xlPart, , , 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
         Else
            [COLOR=#0000ff]Cl.Offset(, Cnt - 1).Value = ""[/COLOR]
         End If
      Next Cl
   Next Cnt
End Sub
 
Upvote 0
Re: Retriece cell contents from a different sheet based on a Lookup/Index-Match over a multi-column/row range

HAHA!! Bang, you nailed it!

That's incredible. Thank you so much Fluff, this is awesome.

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

Glad to help & thanks for the feedback.

Seasons Greetings.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
668
purekarting
P
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
574
Alex Blakenburg
A
S
  • Solved
Extract column data from non-contiguous columns based on header text with duplicate headers
Replies
3
Views
448
Peter_SSs
Peter_SSs
S
  • Question Question
How to Distribute Data in a column/s to other sheets in terms of certain Cell Reference
Replies
0
Views
570
SalamaM
S
B
  • Question Question
cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste
Replies
1
Views
2K
benbulloch
B

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top