Alternative to lookup formulas

[MissingInAction]

Hi everyone. I'm looking for an alternative to the lookup formula. Here is some background. The Excel sheet is for a landfill site, so every time waste gets dumped, the entry gets assigned a unique sequential number. On the invoicing sheet I have calculations to determine how much each company must pay for the waste they dumped. On the invoice it should also list all the sequential numbers relevant to waste that must be paid for (some waste types are free to dump, so they don't need to be listed.) This is where the lookup formula alternative comes in. I had a lookup formula that worked, but now it doesn't anymore and I don't know how to fix it. It also slows down Excel quite a bit.

I have tried vlookup and xlookup, but it only lists the first entry its find not the list. Hope I explained it well.

 

[mamady]

Hi Dear,

Since you have Office 365, you can use the Filter formula.
In order to be able to suggest the exact formula, please share a sample of the data using XL2BB.

Regards
M. Yusuf

 

[MissingInAction]

I make use of multiple sheets, so I'm not sure XL2BB will work for that. Here is a link to the file: Invoicing.xlsm
The waybill data can be found in the Data sheet and the current lookup formula is located at cells I2:I108 on sheet Private.

Thank you

 

[mamady]

I did understand exactly what conditions you are looking for, and could not locate sheet Private. Bt I created a new TEST sheet where I demonstrated the use of the Filter formula so you can adjust.

Regards

 

[MissingInAction]

ah sorry, i forgot I renamed the sheets on the file I uploaded. Private sheet is actually Region 1 sheet. I'm looking at your test sheet now.

 

[Fluff]

@mamady
Please post you suggestion to the board so that everyone can see it. Thanks

 

[mamady]

@mamady
Please post you suggestion to the board so that everyone can see it. Thanks

Sure dear, thanks for letting me know

 

[mamady]

Book1
	B	C
2	Company Name	Company 105
3
4	Transaction ID	General Waste
5	20006844	12700
TEST

Cell Formulas
Range	Formula
B5:B53	B5	=FILTER([Invoicing.xlsm]Data!$A$12:$A$271,[Invoicing.xlsm]Data!$D$12:$D$271=TEST!$C$2,"")
C5	C5	=FILTER([Invoicing.xlsm]Data!$Q$12:$Q$271,([Invoicing.xlsm]Data!$D$12:$D$271=TEST!$C$2)*([Invoicing.xlsm]Data!$L$12:$L$271=TEST!$C$4)*([Invoicing.xlsm]Data!$A$12:$A$271=TEST!B5),"")
Dynamic array formulas.

 

[MissingInAction]

Hi Mamady. I have looked at the formula, but I do not understand it. I'll refer to this formula:

=FILTER(Data!$Q$12:$Q$271,(Data!$D$12:$D$271=TEST!$C$2)*(Data!$L$12:$L$271=TEST!$C$4)*(Data!$A$12:$A$271=TEST!B5),"")

I'm rewriting it to explain what is confusing me:
=Filter(Net Weight Range,(Company Name Range=Company Name)*(Product Range=Product)*(Transaction code Range=Transaction code),"")

I assume the * in the formula is used to multiply? If so, why multiply the company name result, with the product result and then multiply that with the transaction code? I do however realize that the product portion of the formula results in a true/false result, but that just confused me more.

 

[alansidman]

Here is an alternative