Adjust array to column guided by cell value

Test Document.xlsx

The attached document explains my issue. Please refer to cells C3 to E3 on sheet called "Data Presentation", I am trying to make the array inside my MATCH function adjust itself depending on the column where the VLOOKUP value is located (inside sheet called "Mold"). As you can see, the information that is being pulled out of my table does not match with what it is stated in it.

Thanks!

Hi.

You can use the INDEX function to create a dynamic range for your MATCH function.
So, instead of the hard coded Mold!C6:C9, you can try INDEX(Mold!$C$6:$E$9,0,C$2).
Mold!$C$6:$E$9 is the whole range, with all three columns.
The second argument in the INDEX function determines the row number, so when you type 0 (zero) for this argument, the Excel will consider all rows.
The third argument determines the column number, and for that we use the value in the heading row, C2.

I have also shortened up your VLOOKUPS, since you have locked the range to the first row.
The formula in C3 would be like:

Formula:

Please Login or Register  to view this content.

Then you can drag it across.

Good luck!

Hello Estevaoba,

This is just what I was looking for, it works perfectly. I wasn´t aware that a 0 meant that in the INDEX function.
Thanks for helping me out!

You're welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day!