Formula to find data in table with multiple columns based on 2 critera

Question

I am looking for a formula that will look at this table and pull the hours per week based on 2 criteria – group and week.  Right now, I have the tables separated so that I can use a vlookup formula. But that means I have to have 5 different formulas, 1 for each group.

=IFERROR(VLOOKUP(ROUND(($[First Receive]@row  - $[Week 1 Date]@row ) / 7, 0), {TRG_Reference Group1}, 2, false), 0)

This part - ($[First Receive]@row  - $[Week 1 Date]@row ) / 7, 0) is calculating the week number for me based on opening date and current week.

I know there has to be a formula (nested?) that will look at the whole table and return the value for the correct group and week number.

Looking for the wisdom and knowledge of this group to help me.

Thank you!

Paul Newcome · Answer

I too would suggest an INDEX/MATCH instead of a VLOOKUP simply for durability and flexibility. VLOOKUP is very rigid and very easy to break.

Having said that, you can use a nested IF to output the column number pulled by your VLOOKUP. Depending on the group names, if they really are "Group 1", "Group 2", etc., we can use an even more simple formula, but the nested IF would be the same as if it were a stand-lone formula that we would then drop into the appropriate part of the VLOOKUP.

IF(Group@row = "Group 1", 2, IF(Group@row = "Group 2", 3, IF(Group@row = "Group 3", 4, IF(Group@row = "Group 4", 5, 6))))

We would then drop that into the portion of the VLOOKUP that indicates which column to pull from.

=IFERROR(VLOOKUP(ROUND(($[First Receive]@row - $[Week 1 Date]@row) / 7, 0), {TRG_Reference Group1}, 2, false), 0)

=IFERROR(VLOOKUP(ROUND(($[First Receive]@row - $[Week 1 Date]@row) / 7, 0), {TRG_Reference Group1}, IF(Group@row = "Group 1", 2, IF(Group@row = "Group 2", 3, IF(Group@row = "Group 3", 4, IF(Group@row = "Group 4", 5, 6)))), false), 0)

Converting to INDEX/MATCH (preferred method):

The base VLOOKUP conversion to INDEX/MATCH would look more like this:

=IFERROR(INDEX({Group 1 Column}, MATCH(ROUND(($[First Receive]@row - $[Week 1 Date]@row) / 7, 0), "Week Number Column}, 0)), 0)

Here we would create separate cross sheet references for each column and use a nested IF to output the appropriate {Reference} like so:

=IFERROR(INDEX(IF(Group@row = "Group 1", {Group 1 Column}, IF(Group@row = "Group 2", {Group 2 Column}, IF(Group@row = "Group 3", {Group 3 Column}, IF(Group@row = "Group 4", {Group 4 Column}, {Group 6 Column})))), MATCH(ROUND(($[First Receive]@row - $[Week 1 Date]@row) / 7, 0), "Week Number Column}, 0)), 0)

Alan P. · Answer

I would first look into using an INDEX/MATCH combination instead of VLOOKUP. That way it will find your data no matter where it is in your sheet.

INDEX(range, row_index, MATCH(search_value, range, [search_type]), [column_index])

Ex:

=INDEX([Weeks]:[Weeks], MATCH(WEEKNUMBER([Week 1 Date]@row ), {TRG_Reference Group1}, 0), 2)

** The WEEKNUMBER function automatically returns the week number by date. **

I'm not sure exactly how your sheets are working, but this is where I would start.