Combine VLOOKUP and HLOOKUP?

Question

I'm trying to create a spreadsheet for tracking and "scheduling" inventory out during certain date ranges. Inventory can be scheduled out for events that occur during certain date ranges, and during that time inventory might not be available to be pulled for other events if they coincide at the same time. I want to be able to check a date to look up within the list of date ranges in the events list, in order to return the amount of inventory that will be in stock at that time. This would prevent someone from accidentally backordering inventory for multiple events occurring simultaneously.

I currently have a VLOOKUP in E20: =VLOOKUP(E2,Inventory_Table,2,FALSE) that checks the item selected in the inventory pool (named range Inventory_Table) to determine how much inventory exists. I have created an HLOOKUP that finds the item name in the events table and tells me how many are scheduled out =HLOOKUP(E2,Event_List,((row needs to match the date range that was found in the vlookup)),FALSE) As you can see, I need to combine the VLOOKUP into the HLOOKUP in order to pull information from the correct row.

I think the way to accomplish this is supposed to be an Index Match, but I don't know how to combine them correctly. Any help would be appreciated. Thanks!

Answer 1

You want to use Vlookup to return the inventory number and SUMIFS() With INDEX/MATCH to return the number that will be in use on the date provided:

=VLOOKUP(E2,A2:B6,2,FALSE)-SUMIFS(INDEX(C11:G18,0,MATCH(E2,C10:G10,0)),A11:A18,"<=" &E1,B11:B18,">="&E1)