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 SkipVought,

In the Table Design I have renamed my two tables tMaster and tArtwork
When I first opened your table I could see
=tMaster[[#Headers],[ID]]
=tMaster[[#Headers],[Name]]
=tArtwork[[#Headers],[ID]]
=tArtwork[[#Headers],[Column1]]
=tArtwork[[#Headers],[Item]]

But now I can't, I must have been in a setting somewhere.
I tried to add these into the Header cells, but that is not working
How and where do I add this information?
Sorry to keep asking you all these questions.
Thanks for your help

 

[SkipVought]

I don't know what you mean by "opened your table"

If you reference the elements of the Structured Table from outside the table, that is what you might see.

If the table somehow became UNstructured, then you need to make if structured via Insert > Tables > Table

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi SkipVought,

I think I have sorted it,
It did not like Merged cells, so I had to remove the merged cells
so thank you so much for all your help

Now is it possible to Modify =OFFSET(tArtwork[ID],MATCH(H2,tArtwork[ID],0)-1,2,COUNTIF(tArtwork[ID],H2),1) show all the information in the cells to the right
A13 - B13 - C13 - D13 etc
A14 - B14 - C14 - D14 etc
A15 - B15 - C15 - D15 etc

 

[SkipVought]

What columns in tArtwork correspond to columns B, C, D etc in Profile sheet?



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi SkipVought,

C has the "Item" info - This is the information we have retrieved for A13, A14 and A15
So the next columns are D to S

 

[SkipVought]

Maybe this in A13 and copy/paste across...

A13: =OFFSET(tArtwork[ID],MATCH($H$2,tArtwork[ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[ID],$H$2),1)



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

It gave me a message - "Spill", it said it would put the info in the neighbouring blank cells, so I said ok
But then there was nothing in the neighbouring blank cells

 

[SkipVought]

My example below

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

...and be sure that NOTHING, not even SPACES, are in ANY of the cells in the MAX range that the arrays will fill

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Sorted
Just realised that you had the Formula in A13, A14, A15 Etc
Brilliant, thank you so much

 

[SkipVought]

My formula is only in row 13.

The OFFSET() function returns an array. In Excel365 and Excel 2021, I believe, handles rendering arrays into the appropriate cells. It is those rendered array cells that cannot contain ANYTHING, not even SPACES.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Sorry
I mean A13, B13, C13 Etc
So Yes just Row 13

 

[SkipVought]

Good! Glad it's working the way you need.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi Skip,

I took your advice and removed the vlookup and replaced with Index/Match

This works most of the time =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0))
I was getting a result some times of 0
So I changed it to =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0))&""
Which cured the problem of the result of 0
but does not work with Dates so used =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0)) for dates which worked but then if the date cell was blank it returns 00/01/1900
I have tried =IfError(INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0)), "") but returns 00/01/1900 if the cell is blank and the correct date if the cell has a date

Can you help please?

 

[cneill]

Hi Skip,

Regarding =OFFSET(tArtwork[ID],MATCH($H$2,tArtwork[ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[ID],$H$2),1)

Is there away that would allow the information to be entered into a Merged Cells so I don't get the "Spill" Error

Thanks for your help

 

[cneill]

Sorry Skip,

Regarding =OFFSET(tArtwork[ID],MATCH(H2,tArtwork[ID],0)-1,2,COUNTIF(tArtwork[ID],H2),1)
Thanks

 

[SkipVought]

1) Regarding dates: I would use Conditional Formatting to make the Font Color the same shade as the Cell Color when the Cell value is 0.
faq68-5827

2) Regarding merged cells: I can't visualize what problem you're having here. But in general, merged cells present a challenge. At a minimum, I'd need to observe what you're referring to, like a screenshot. Ideally, I'd like to have a representative example in an Excel workbook in which I could maneuver.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi Skip,

I have attached a representative sample.

1.) Dates
In the Cells on the Profile Page you will see I have used =INDEX(tMaster[Date of Birth],MATCH(E2, tMaster[Master ID],0)) &"" for all the cells except the dates
when I use =INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0))&"" for a date cell - See "Burial Date" it does not return the correct Date format
when I use =INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0))&"" if the cell is blank on the Master List then it is is blank on the Profile Page
If I use =IFERROR(INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0)),"") I get 00/01/1900 if the cell is blank on the Master List sheet

Further down on the Profile page rows 15 to 25 where it returns the results from the tArtwork sheet
In cell A17, I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[Master-ID],$E$2),1),"No Information") I added an IfError to return "No Information" when there was No Information

In Cell C17, I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[Master-ID],$E$2),1),"")This time I added the IfError to return ""

In Cell F17 I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()-1,COUNTIF(tArtwork[Master-ID],$E$2),1),"") This time I added the IfError to return ""
But I had to modify the Column() from +1 to -1 as the +1 returned the wrong Column on the tArtwork sheet

"Spill Error"
If you now merge A17 and B17 or C17 and D17 or F17 and G17 you get the spill error

The Idea of the Profile Page is to have a quick over view of all the information in one place - Is this the best way to do this?
I hope I have made myself clear for you
Any Questions let me know

 

[SkipVought]

As I stated earlier, merging cells presents some challenges. This is one of those situations in which merge cannot be used.
Be advised that the Column Offset in the OFFSET() function (argument 3) must correspond to the column number minus 1 in the tArtwork table.

Regarding dates prior to Jan 1 1900, date formatting has no effect. Rather, since all your dates are prior to 1900, change the Number Format to General and concatenate a "" to each formula.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

...and in addition

You have a relatively complex set of requirements that we got in dribs and drabs. We just got a peek at your data structure and content.

If I had additional DATA for at least 1 more composer, I'd be able to test another interactive solution for displaying Item, Creator & Title in individual columns using the Camera feature. This would involve VBA, where changing the SelectedID, the Range Name I assigned to E2, would filter tArtwork and display the filtered result on the Profile sheet.

I have used the Camera Feature in a similar circumstance, where I had selection data above the of selected data where the summary data columns did not fit the table data columns.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!