Alternative for Vlookup to search for a column rather than a row

Matthew Emrich

I'm trying to simplify a formula that works, but will become too long for the cell. The Current Formula is

=IF([Equipment Max#]@row = "", "", IF([Equipment Required]@row = "FOB", IF(COUNTIFS({EDBFOB}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error"), IF([Equipment Required]@row = "ERG", IF(COUNTIFS({EDB ERG}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error"), IF([Equipment Required]@row = "OCT", IF(COUNTIFS({Equipment Data Base OCT}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error"), IF([Equipment Required]@row = "JET", IF(COUNTIFS({Equipment Data Base JET}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error", ......... goes on for more equipment.

What I'm trying to accomplish with this formula is tell me if I'm over allocating my equipment. By looking up the equipment needed in the row and referring to another sheet to determine how often that machine is being used in the given time frame that it is required for, if there is more equipment being used than is available, it returns an error value, which I will then use to create a conditional format to show that the equipment is overallocated.

The issue I'm having is in the other sheet the values I'm looking for are the columns (so I can't use Vlookup), the rows on the lookup sheet are the dates.

I want to simplify the formula so it looks for the equipment required in the row, on the other sheet and automatically looks in the correct column for the date range instead of having to manually use an if statement to look what equipment is being used in the row and then having to manually put the column range to look for the dates in.

This is the sheet I want to pull the data from

This is the sheet that will receive the value

Thanks for the Help

Matthew Emrich

@Paul Newcome looking at other posts, it seems like you're an expert in Index and Collect. Can you have a look at my issue here please?

Paul Newcome

I'm not sure I follow...

Are you able to provide some screenshot with MANUALLY entered data that reflects the desired outcome?

Matthew Emrich

@Paul Newcome So this sheet records the different studies going on, the amount of time they will be running for and the type of equipment is needed. The sheet below looks at the above sheet to determine how many of the same type of equipment are being used on a given day. What I need to do is on the sheet above, put an error in the column if the amount of equipment being used is more than the max equipment available. I have manually entered the data in the images to show the desired outcome.