Issue with Index&Match formula

[Jyggalag]

Dear all,

I am currently trying to return some data from a larger database. I have created a test sheet that is very similar to the real sheet that I am working with, for the sake of privacy and making things easier to understand from an outside perspective.

Currently, I have the following (please ignore column B, D and E and everything but cell C1 in column C.

The formula pulls data from this master data sheet, called "Test data transposed":

My issue is currently that the formula only works for the first value, I.E. cell F2. Once I pull it down to cell F8 it just leaves cells F3-F8 blank.

Can anybody help me fix this so the values in F3-F8 return the correct values?

Apologies for any confusion - please feel free to ask questions and ask me for more data or screenshots as well!

Kind regards,
Sheogorath

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Issue with INDEX&MATCH formula not pulling through multiple values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Jyggalag]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Issue with INDEX&MATCH formula not pulling through multiple values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Dear Rory,

Thank you and sorry. The other thread is concerning a different issue, however I can see why they may seem similar.

I would very much like some assistance with this specific formula if possible

 

[Jyggalag]

Dear Rory,

Thank you and sorry. The other thread is concerning a different issue, however I can see why they may seem similar.

I would very much like some assistance with this specific formula if possible

Oh nevermind, my bad! I apologize for the cross-post.

Here is a link to the other forum, but I would actually prefer to have my answer in this forum (this is my favorite forum, but I feel like traffic is a bit slow right now): Issue with INDEX&MATCH formula not pulling through multiple values

 

[RoryA]

You have swapped the row and column arguments round (row is first, then column) in your INDEX formula, and your ranges don't match up. You refer to rows 5:15 in the first argument to INDEX, but your second match formula uses rows 4:34

 

[Alex Blakenburg]

I know you have received the same advice on the other forum but you might find this helpful:
(Put in F2)

=IFERROR(INDEX('Test data transposed'!$D$4:$F$34;MATCH($G$2;'Test data transposed'!$K$4:$K$34;0);MATCH($C$1;'Test data transposed'!$D$4:$F$4;0));0)

And if you want a clearer version to either understand or use try this:
(They are identical just with a different layout which doesn't impact their operation)

=IFERROR(
                 INDEX('Test data transposed'!$D$4:$F$34;
            MATCH($G$2;'Test data transposed'!$K$4:$K$34;0);
            MATCH($C$1;'Test data transposed'!$D$4:$F$4;0))
       ;0)

 

[Jyggalag]

I know you have received the same advice on the other forum but you might find this helpful:
(Put in F2)

=IFERROR(INDEX('Test data transposed'!$D$4:$F$34;MATCH($G$2;'Test data transposed'!$K$4:$K$34;0);MATCH($C$1;'Test data transposed'!$D$4:$F$4;0));0)

And if you want a clearer version to either understand or use try this:
(They are identical just with a different layout which doesn't impact their operation)

=IFERROR(
                 INDEX('Test data transposed'!$D$4:$F$34;
            MATCH($G$2;'Test data transposed'!$K$4:$K$34;0);
            MATCH($C$1;'Test data transposed'!$D$4:$F$4;0))
       ;0)

Alex you are my savior <3

Thank you so much my friend, you've truly made my day!

The formula works perfectly and I'm gonna note it down for future use, I truly cannot express how grateful I am for your assistance

Sorry for the mess everybody. I have marked Alex' answer as the correct one now

 

[Alex Blakenburg]

No problem. Glad we were able to help and thank you for the feedback.