Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
445
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

1632128954216.png


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

1632128980416.png


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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I know you have received the same advice on the other forum but you might find this helpful:
(Put in F2)

Excel Formula:
=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)
Excel Formula:
=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)
 
Upvote 0
Solution
I know you have received the same advice on the other forum but you might find this helpful:
(Put in F2)

Excel Formula:
=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)
Excel Formula:
=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 :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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