Index match with multiple criteria

Im not sure what your expected outcome would look like - can you explain?

In J3, copied down??

=IFERROR(INDEX(INDEX($B$3:$G$6,MATCH($I$3,$A$3:$A$6,0),),AGGREGATE(15,6,COLUMN($B$2:$G$2)-COLUMN($B$2)+1/($B$2:$G$2<>"Cost"),ROWS(J$3:J3))),"")

Thank you both for the incredibly fast responses. I see both of your answers a lot whenever I search for something in the forum and have been able to apply several to the workbook I am currently working on. As for the formula, it would not work at first, until I tested it on the same worksheet as the source data. I should have said that I expected to see the output in a separate worksheet. Apologies. How can it be modified to do that?

Glenn, thank you so much for the assistance. This is the second time since I joined a few weeks back.

What is the name of the worksheet that the data is being TAKEN FROM?

Don't worry about it. I have to go out now.


see modified file. I'll be back in 1-2 hrs if you still have issues. but for now....


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Duhhh! Now with modified file...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Glenn, I have one more question in connection to the formula. I realised it might be better to display the results in helper columns on the same sheet as the data. What should I change in case I were to drag the results across?

Try this:

=IFERROR(INDEX(INDEX($B$3:$G$6,MATCH($I$3,$A$3:$A$6,0),),AGGREGATE(15,6,COLUMN($B$2:$G$2)-COLUMN($B$2)+1/($B$2:$G$2<>"Cost"),COLUMNS($J3:J3))),"")

change the bit in red to wherever the formula is.

However, if you change your mind, or it doesn't work as desired... post a sample sheet showing what you want, where you want it... as this is turning into a guessing game, held in the dark!!

Glenn! Thank you!

I am learning things as I go. I started a workbook last month that I didn't realise was going to get so complex. Before that time, I only knew how to do basic sum, product functions, not knowing what vba was nor index match. So there are times when it's difficult to even describe what I need, let alone succinctly, on the title of a thread.

But I'll make sure to be clearer in future posts. Anyway, the formula works great. As was the description - a guessing game in the dark - poetic .

To sum up for others who come across the thread. I wanted a formula that would retrieve several cells associated with a certain match entry.

Glenn gave two formulas that did such. One that can be dragged down into rows and another that can be copied across columns.

=IFERROR(INDEX(INDEX($B$3:$G$6,MATCH($I$3,$A$3:$A$6,0),),AGGREGATE(15,6,COLUMN($B$2:$G$2)-COLUMN($B$2)+1/($B$2:$G$2<>"Cost"),ROWS(J$3:J3))),"")

=IFERROR(INDEX(INDEX($B$3:$G$6,MATCH($I$3,$A$3:$A$6,0),),AGGREGATE(15,6,COLUMN($B$2:$G$2)-COLUMN($B$2)+1/($B$2:$G$2<>"Cost"),COLUMNS($J3:J3))),"")

Hahaha. you're welcome!!