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.

 

[jerry12302]

You can also use SUMPRODUCT in a similar way, and, for the benefit of others reading this thread, you don't need Office 365, SUMPRODUCT has been around in Excel for years. I use it, or a combination of INDEX and MATCH, depending on the complexity of the data.

The multiplication of the Company Name, Product, and Transaction Code results is used to determine if the Net Weight data in a particular row in the array should be added. TRUE and FALSE results are converted to 1 and 0. Any result that returns FALSE will result in an overall product of 0 for a row, and the data for that row will not be added. The data for any row will only be added if all the criteria results in that row translate to TRUE, and therefore 1.

There are hundreds if not thousands of articles online that explain how to use SUMPRODUCT, which can also incorporate an OR criteria, I'm not sure if FILTER can do that. Just google "Excel sumproduct function explained".

 

[MissingInAction]

Thank you for the info. I'll have a more in depth look at the posts as soon as I can.
Just to clarify, I do not need the formula to look up the weight, it needs to look up the transaction code of the selected company. Kinda like a pivot table would do. I then have a different formula determining if the waste type of the transaction code, returned by the previous formula, is a type of waste that must be paid for.

 

[jerry12302]

That was an example to demonstrate the use of SUMPRODUCT. If you just need to lookup a transaction code based on one cell value, the selected company, a simple combination of INDEX and MATCH will work fine. VLOOKUP will not work if the data is not sorted alphanumerically. INDEX and MATCH work whether the data is sorted or not. Like this:

	=INDEX(H6:H10,MATCH(E6,G6:G10,0),1)
	E	F	G	H
	Selected Company:	Result:	company	transcode
6​	DEF Corporation	mnop	XYZ Company	abcd
7​			Any Company, LLC	efgh
8​			Some Other Company	ijkl
9​			DEF Corporation	mnop
10​			B.D. Company, Inc.	bcdz

 

[MissingInAction]

Hi Jerry. That formula looks promising. If a company has more than one transaction number, how do I get it to list the 2nd, 3rd, etc. transaction code?

 

[jerry12302]

I believe if there are more than one unique transaction codes for a particular selected company you would need to run a macro to create a list of those codes for that company.

The other option would be to analyze the data and determine the maximum count of different transaction codes any company would have, or would ever likely have, which could be done with a SUMPRODUCT function, then create a table with multiple functions. So if you would realistically only ever have a maximum of 5 (for example) different codes for any company, instead of just one function you would have 5 rows of that function. It would have to be modified to prevent duplicate codes in subsequent rows, which should not be difficult.

To prevent misleading information, because suddenly one day there are 6 codes for a company, you could test whether you have sufficient functions by counting the number of existing functions compared to the new quantity of unique codes for each company. That could be placed at the top of your worksheet with conditional formatting to highlight the difference and notify you that you need to expand the number of rows of functions to accommodate the new maximum number of unique codes.

If I were doing this, I would create a macro to make a list of all the codes for a selected company.

 

[MissingInAction]

Thanks, but that seems like more effort than it is worth. I managed to fix the lookup formula in the mean time, so thanks everyone for the suggestions.

 

[jerry12302]

OK, just remember that VLOOKUP will not work properly if your data range is not sorted.

I worked out a non-macro solution using a combination of MATCH and INDEX functions, and also highlighted when duplicate codes exist for a company.

I am trying the Mini-sheet upload for the first time, so please bear with me...

find company codes.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
3	2 More Result Rows Needed	Duplicate Codes Exist	<---- Modify the formulas in D3 and E3, and column O, to accomodate the entire number of rows as applicable when company data and/or the company list, or the range of Results formulas, is expanded
4
5		Selected Company:			Result:	Duplicates		Company	Trans Code		Company List	Extra Result Rows Needed
6		DEF Corporation		8	ABCD	ABCD		XYZ Company	10WW		Any Company, LLC
7				17	EFGH			XYZ Company	ABCD		B.D. Company, Inc.
8		If more rows are needed		19	ABCD	ABCD		DEF Corporation	ABCD		ABC Company, Inc.
9		for the Result formulas,		21	LMNO			Any Company, LLC	ABCD		Some Other Company
10		as shown in D3 above,						B.D. Company, Inc.	ACW3		XYZ Company
11		select the last row of						B.D. Company, Inc.	ACW9		JJ, Inc.
12		formulas in G thru I and						B.D. Company, Inc.	ACW3		DEF Corporation	2
13		drag them down the						B.D. Company, Inc.	BC1Z
14		number of rows needed.						ABC Company, Inc.	EF12
15								Some Other Company	EF12
16								Any Company, LLC	EFGH
17								DEF Corporation	EFGH
18								Some Other Company	EF13
19								DEF Corporation	ABCD
20								JJ, Inc.	IJKL
21								DEF Corporation	LMNO
22								DEF Corporation	ABCD
23								JJ, Inc.	EFGH
24								DEF Corporation	99ZZ
25								JJ, Inc.	IJKL
Sheet1

Cell Formulas
Range	Formula
D3	D3	=IF(SUM(O6:O12)>0,MAX(O6:O12)&" More Result Rows Needed","Result Rows are Sufficient")
E3	E3	=IF(COUNTA(I6:I10)-COUNTIF(I6:I10,"")>0,"Duplicate Codes Exist","No Duplicate Codes")
G6:G9	G6	=IF(G5=COUNTA($K$6:$K$25)+ROW($K$5),"",IFERROR(MATCH($E$6,INDIRECT(ADDRESS(G5+1,COLUMN($K$5))&":"&ADDRESS(COUNTA($K$6:$K$25)+ROW($K$5),COLUMN($K$5))),0)+G5,""))
H6:H9	H6	=IFERROR(INDIRECT(ADDRESS(G6,COLUMN($L$6))),"")
I6:I7	I6	=IF(ROW(H6)=ROW(H$6),IF(ISERROR(MATCH(H6,H7:H$25,0)),"",H6),IF(ROW(H6)=ROW(H$25),IF(ISERROR(MATCH(H6,H5:H$6,0)),"",H6),IF(AND(ISERROR(MATCH(H6,H5:H$6,0)),ISERROR(MATCH(H6,H7:H$25,0))),"",H6)))
I8:I9	I8	=IF(ROW(H8)=ROW(H$6),IF(ISERROR(MATCH(H8,H9:H$25,0)),"",H8),IF(ROW(H8)=ROW(H$25),IF(ISERROR(MATCH(H8,H$6:H7,0)),"",H8),IF(AND(ISERROR(MATCH(H8,H$6:H7,0)),ISERROR(MATCH(H8,H9:H$25,0))),"",H8)))
O6:O12	O6	=IF(COUNTIF($K$6:$K$25,N6)-COUNTA($G$6:$G$9)<=0,"",COUNTIF($K$6:$K$25,N6)-COUNTA($G$6:$G$9))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L22	Expression	=K22:K38=$E$6	text	NO
L25	Expression	=K25:K41=$E$6	text	NO
L23	Expression	=K23:K39=$E$6	text	NO
L24	Expression	=K24:K40=$E$6	text	NO
K23:K25	Expression	=K23:K39=$E$6	text	NO
K22	Expression	=K22:K38=$E$6	text	NO
O6:O12	Cell Value	<>""	text	NO
L6:L21	Expression	=K6:K22=$E$6	text	NO
K6:K21	Expression	=K6:K22=$E$6	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E6	List	=$N$6:$N$12