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

 

[SkipVought]

Hi,

In sheet Art and Sculpture, does any artist appear more than once?

It's odd that that sheet has 147 entries while your Master sheet has 5000???



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,

The Tile of Art and Sculpture slightly misleading
The name of the person is the Musician

Art and Sculpture sheet contains the information about his or her Portrait, or Bust
Yes the Musician could have up to two items associated with them

Yes the Art and Sculpture sheet only has a small number of records compared to the number of musicians

 

[SkipVought]

So you want to see the notation from the Art and Sculpture sheet next to each name in your Master List column E?

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 was really thinking that, I would like put the name from the Master List next to the notation information on the Art and Sculpture sheet, as the Art and Sculpture sheet is additional information associated with the name on the Master List.

Do you think that is the correct way to do this?

 

[SkipVought]

One sheet has about 150 entries while the other has over 5,000.

You apparently have a one-to-many relationship. So you can't do what you want to do, or else I'm totally misunderstanding your data structure.

Or are you saying that here are about 150 entries for Franz Joseph Hayden on A & S ?

I was Hayden in Bach trying to get a Handel on things from the Liszt I had.


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,

The Master List, has the name of the musician just once
The Art and Sculpture has the information for many different musicians and some musicians have multiple Items e.g. a Portrait and Bust
so there would be two rows of information about each item both connected to a single musician.
so it would be a one-to-many relationship

 

[SkipVought]

So, why only 3:150? Does not make sense when you have over 5,000 Names?

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,

Yes I appreciate it sounds odd
the 5,000 names are a membership list
the 150 items are simply owned by the music society, so there are 130 Portraits of different members and 12 busts of different members and some have a Portrait and a Bust
I hope that helps

 

[SkipVought]

So is Liszt on your list?

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,

No

 

[SkipVought]

So not every Member has a artifact?

Do any members have multiple artifacts?

What is the maximum number of artifact records for any member currently?

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,

So not every Member has a artifact? - Correct

Do any members have multiple artifacts? - Yes

What is the maximum number of artifact records for any member currently? - 3

 

[SkipVought]

What version of Excel are you using?

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]

Assuming some things and simplifying an example, I offer 2 solutions: 1 for Excel 365 and 2 for other versions greater than Excel 2007.

Both employ the use of Structured Tables. Below is a screenshot of example 2

Both examples use the OFFSET function to grab the array of artwork associated with the composer's ID.

The difference between examples 1 & 2 is the use of the UNIQUE() & TRANSPOSE() functions in 1, while 2 uses an index in ROW 1 and the INDEX() function.

If the assumptions I assumed are correct, you'll need to make adjustments to accommodate the formula to your data structure and probably make your tables Structured via Insert > Tables > Table.

Solution 2 link below.

[highlight #D3D7CF]Musical puns are my f.
It was my conviction that over his lifetime, Franz Liszt compiled a compendium of a conglomerate of composers' compositions. Musical connoisseurs, therefore, lust for the last of Liszt's lists, lest Liszt's lists be lost.[/highlight]

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]

Thank SkipVought
Sorry for the delay, I have been away for a few days
Looks great
I will check it out and let you know if I have any other questions
Thanks for your help

 

[cneill]

Hi SkipVought,

I am trying to get your code to work in my Spreadsheet, but am not doing very well, I have changed a few things so I would now like to use your code to pull various items of information related to the person on to a Profile Page.
So when you select from the dropdown list, populated from column A on the 'Master List' Sheet. This will then list all the ID's then using =IFERROR(VLOOKUP(H2, 'Master List'!A5:H5009,8, FALSE),"<<< Add the Person's ID") so I can just select one ID Number from the list to show the name of the person in Cell I2 on the Profile Page.

=IFERROR(INDEX(OFFSET(tArtwork[Item],MATCH(tMaster[@ID],tArtwork[ID],0)-1,0,COUNTIF(tArtwork[ID],tMaster[@ID]),1),H$1),"")

tArtwork = 'Artwork and Sculpture' Sheet
Item = column C on the 'Artwork and Sculpture' Sheet
ID = column A on the 'Artwork and Sculpture' Sheet

tMaster = 'Profile Page' Sheet
@ID = H2 on the 'Profile Page' Sheet

I would like your H$1 to use the code to populate A13 on the 'Profile Page' Sheet
I would like your I$1 to use the code to populate A14 on the 'Profile Page' Sheet
I would like your J$1 to use the code to populate A15 on the 'Profile Page' Sheet

So when the ID is selected in H2 on the 'Profile Page' Sheet, the related information is populated in A13, A14, A15 on the 'Profile Page' Sheet

I hope this makes sense and you are able to help me
Thanks again for your help

 

[cneill]

Hi SkipVought,
Just to let you know I am using Excel 2021

 

[SkipVought]

 

[SkipVought]

You can see in my uploaded example, this is a much simpler solution than I previously imagined.

I used an in-cell Data > Validation > List for the dropdown in H2. The caveat when using Structured Table names is to add a Range definition, in this case rID for the tArtwork[ID] to use in the dropdown reference.

FYI: I never, no, never, what, never?, hardly ever, use the VLOOKUP() function. I almost always use INDEX & MATCH being that VLOOKUP requires the lookup column to be the first column in the reference range, which in my world, rarely occurs.

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!