Index and Vlookup Question

D

DM_86

New Member
Joined
Aug 15, 2017
Messages
3
Hi All,

I'm hoping someone can please assist me with this (possibly tricky) query:

I have a master sheet and then a report sheet in a single workbook.

Essentially, I want the formula's in the Master sheet, to update automatically based on the report sheet month being updated. Sometimes the alignment of columns in the report sheet changes month to month.

I also need to restrict the rows to only those that have a State or "NSW".

Below is an example of the data, but essentially, I'm thinking that I need to place something along the lines of the following in Cell D2 in order to pull the data from the 'Update' Column in worksheet 'Report - July'

=VLOOKUP(A2,INDIRECT("'"&AJ2&"'!"&"Column D"),2,0)

However, this formula doesn't quite work and also doesn't remove other states. Ideally, this formula would also include a means of updating the prior month automatically within the cell formula (rather than referencing AJ2). This could look something like this (however, I'm not sure how to incorporate this into the above formula):

=CONCATENATE("'Report - "," ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM'")))

Master Sheet: Called 'Master'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column AJ[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Report - July[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Report Sheet 1: Called 'Report - July'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]

Report Sheet 1: Called 'Report - August'

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Even[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4076[/TD]
[TD]Bad
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much, any help is greatly appreciated.

Cheers,

Dave
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try something like this... I did a few tests and got this to look up data off of a sheet name "Report - July"

Code: 
=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"A:D"),2,0)
 
Upvote 0
Hi mrmmickle1,

Thank you very much; this answer is very close and seems to be on the right track but doesn't quite return the correct result.

If I put this formula in cell D2 it returns the value in the 'Report - July' worksheet but results in cell B2 rather than F2. In other words it puts the state column in the update column.

Apologies if I didn't explain this very well.
 
Upvote 0
You need to change the reference in the Vlookup formula..... 2 = Column 2 which is the State Column.... if you need another column's data just adjust accordingly. In addition you may need to adjust A:D to A:F if you are looking for a value in Column F (Column 6):

Code: 
=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"[B][COLOR=#FF0000]A:D[/COLOR][/B]"),[COLOR=#FF0000][B]2[/B][/COLOR],0)

maybe this will work:

Code: 
=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"[B][COLOR=#FF0000]A:F[/COLOR][/B]"),[B][COLOR=#ff0000]6[/COLOR][/B],0)
 
Upvote 0
Thank you very much Matt,

That works perfectly!

Your help is greatly appreciated.

Cheers,

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

Similar threads

B
  • Question Question
Summing totals in a dynamic list
Replies
4
Views
1K
Fluff
Fluff
S
  • Question Question
Multiple columns lookup
Replies
0
Views
262
Sipirili2504
S
H
  • Question Question
Multiple Reports Master Sheet Question
Replies
7
Views
322
awoohaw
awoohaw
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
231
campbell1093
C
L
  • Solved
Option to combine single row from many tables into one new table
Replies
3
Views
376
awoohaw
awoohaw

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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