Looking up Data

[jscalem]

I am trying to lookup data based on multiple criteria. I have a vlookup with the if(and) function. I have attached an example.


Cell B2 Will change depending on the tab name for each Month.

I need starting with cell B7 to find the data that matches the for the team member, the stat month in cell b2 and the week in the cell b6-f6. I have the columns named the same as the data headers on the weekly team stats page. However that will not always be the exact same on the final project. So I will need to look based on a column number that I can change. If that makes any sense lol sorry if that is confusing.


I need to do this using ONLY formulas. I can't have any macros in this as it will need to be emailed & can't send workbooks with vba in them.

Thank you :-)

[chatz86]

Take a look of attachment. Hope that helps.

Michael ScoreCard Numbers HELP-2.xlsm

[FDibbins]

I need to do this using ONLY formulas. I can't have any macros in this as it will need to be emailed & can't send workbooks with vba in them.

Then I suggest you change the format you saved it in from .xlsM to .xlsX .xlsM is macro-enabled, others might not be able to open it

[FDibbins]

Try this regular formula, copied down and across for the 1st Name, copied down and across...
=IFERROR(INDEX('Weekly Team Stats'!$E$2:$U$49,MATCH($B$2&B$6&$B$5,INDEX('Weekly Team Stats'!$B$2:$B$49&'Weekly Team Stats'!$C$2:$C$49&'Weekly Team Stats'!$D$2:$D$49,0),0),MATCH($A7,'Weekly Team Stats'!$E$1:$U$1,0)),"")
You will have to adjust the bolded parts for each name-block

@chatz86...not sure if it matters (probably not?) but you left the month out.
Also be wary of using full-column ranges like that in ARRAY formulas, they will start to slow the file down

[jscalem]

@FDibbins


This is perfect!!! Thank you so much. I need to utilize index more. I don't use it as much as I should THANK YOU THANK YOU THANK YOU.

[FDibbins]

Happy to help