Multiple Scenario Lookup

[rngadams]

Hello, I have a spreadsheet that has multiple scenarios for lookup and having trouble completing the formula. I have attached a sample sheet.
1. If there is a number in column B on the data tab enter the Program title that corresponds from lookup tab. Easy enough I know. But that number is not always provided. If not then we need to look up the Customer Group (Colum D on data tab) what material was sold (Column E data tab) and if PO Date (Column A data tab) fell in a date range from lookup tab, then enter the Program Title (from column B lookup tab)in column C on Data Tab. I have attached a sample sheet.

[XLent]

You don't provide any expected results so below is an interpretation - particularly in relation to Customer Group & Date requirement.

Please Login or Register  to view this content.

with the above in place, 3 of your rows would fail to return a valid match, rows 10, 12 & 13

[rngadams]

I'm sorry if I wasn't clear, the Program Title in Column C on the data tab is what I was wanting. My date range on the no valid match was because of the date did not fall in range. Your formula worked! However, it said just to give an example so I only gave you the information that was pertinent to the lookup. On the spreadsheet that I am working on. The lookup tab has 36 columns and 132 rows. And the data tab has 1785 rows and 152 columns. I am going over information now but I have come to information that should fall within range and came back with no valid match. I am not familiar at all with the Aggregate(15,6,Row function would that throw off the formula with the rows and columns I have said are in my worksheet? Thanks for all your help!

[XLent]

Your formula worked! However, it said just to give an example so I only gave you the information that was pertinent to the lookup. On the spreadsheet that I am working on. The lookup tab has 36 columns and 132 rows. And the data tab has 1785 rows and 152 columns.

I suspect the key to getting it to work in real-life, so to speak, is the 152 columns you reference on the data tab.

In your sample you had only one PO date, PCR (potentially), Customer Group & Material entry per row -- is this different in the 152 column setup?

In general terms the AGGREGATE is just trying to find the first row in which all criteria are satisfied
Given the size of your data this may not be overly efficient but, conceptually, it should work.

It might be worth compiling a smaller sample which reflects the dimensions of your data - i.e. entirety of lookup info but perhaps limited to a handful of data rows that are causing you issues.

[rngadams]

Thank You so much for your help! I have another lookup and was trying to use the formula that you gave me and I couldn't get it to
work. This time I don't have a simple Vlookup. I have 3 criteria to meet. If Description, Customer and Category are met from data tab on the lookup tab, I need the price from the lookup tab. I have attached a small sample sheet. On my sheet, what I am using for Description , Customer and Category are formulas , not sure if that matters.

[XLent]

If the combinations are unique to the price index (should it exist) then use SUMIFS

Please Login or Register  to view this content.

above would return 5, 8 & 11 for your 3 selections.

[rngadams]

Thanks! Would it make a difference in the lookup if any of the Description , Customer and Category was generated by a formula?