Hi, I am trying to get my head rou

[cneill]

Hi,
I am trying to get my head round INDEX MATCH for the first time but am not doing very well

Could someone help me with this please

I tried to use =INDEX('Master List'!E5:E5:E5020,MATCH(E5,IF('Master List'!AQ5:AQ5020=C3, 'Artwork and Sculpture'!C3:C150),0))
But the result is just "value"

The column in the sheet 'Master List'!E5:E5020 has the name of the person I am trying to look up

The column in the sheet'Master List'!AQ5:AG5020 has records with a Unique ID Number

The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number

I want to look up the name of the person from the Master List and add the name to the cell on the 'Artwork and Sculpture' sheet next to the "C" Column in the "B" Column

Example: Franz Joseph Hayden's name is in the column in the sheet 'Master List'!E5:E5020
The column in the sheet 'Master List'!AQ5:AG5020 has records with a Unique ID Number in this case the Unique ID Number is "1"
The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number of "1"
So 1 needs to match to 1 and Franz Joseph Hayden to be the result in the sheet 'Artwork and Sculpture' in cell B3

Any help would be great appreciated

 

[cneill]

Hi Combo,
I think I have now made all your changes
When I go into the Power Query
It is telling me
Expression.Error: The column 'Master ID' of the table wasn't found.
Details:
Master ID
In the code it says = Table.TransformColumnTypes(Source,{{"Master ID", Int64.Type}})

Also my workbook is now linked to the sample data workbook, so I have now unlinked this, as I need it to be standalone wookbook
Also after I added =@INDEX(tMaster[Name],@IndexMasterID) on the Profile page next to cell E2 it changed to #Ref!
and the code stops at >> If Not Intersect(Target, Me.Range("MasterSel")) Is Nothing Then

I must be missing something, any Ideas?

 

[cneill]

Hi Combo,

Forgot to say
When I add =INDIRECT("tMaster[Master ID]") to the data validation for E2 on the profile page it gives me a message to say "The Source currently evaluates to an error, do you want to continue Yes or No
What have a missed

 

[combo]

So it is likely that the name of the column was changed. Power Query formulas are case sensitive.
To debug PQ queries, select query, select the first action in query, and check the resulting view. If it is ok, select next action, etc.

I don't understand the '@' in =@INDEX(tMaster[Name],@IndexMasterID). INDEX is an Excel function, IndexMasterID is a name, in both cases '@' shouldn't be there.
'@' is used to reference a cell in column in the same row as calling formula.

combo

 

[cneill]

Hi Skip & Combo,
Just a quick thank you for all your help

Combo
I got your Power Query to work, which is great, but in the end I settled for a more simplistic approach and used the =Filter function
Which is easier for me to implement.

One other question to you both
If I use the "Master ID" on the tMaster table to filter the records, is it possible (if there is a corresponding "Master ID" match on The tArtwork table) to filter the tArtwork table at the same time?
Your thoughts would be great

 

[combo]

You need VBA. There is no special event for filter change. Instead, you may create formula to calculate sum of 'Master ID' visible entries (default for table's totals row). Then Calculate event can catch the filter change. Next you need to code: (a) visible ID(s), (b) apply (a) in to build filter in second table.

combo