Vlookup, variable lookup table array

[Hutchisonryan]

I've been working on this project for quite some time, and I've found solutions that get me close, but don't solve my problem. I typically like to figure things out on my own, because I feel that's how you learn best, but after months of work I'm finally breaking down and asking for help (mainly because my VP caught wind of my project, and wants to roll company wide.) So to be able to understand my question fully, I will explain in detail:

This is a performance tracker for servers in a restaurant. The spreadsheets operates in a way so that I can export a report from my POS (Point Of Sale) PC, to my desktop PC via a network drive. I can then copy and paste the report in my workbook and the spreadsheet will auto-populate. There are two reports I'm using. The first is static, and I can auto-populate by stacking Index(Match(Offset no problem. These are basic stats like sales, comps, PPA etc. The second part of the report has to deal with specific focus items sold per individual. Here is where it's getting messy. This report changes size every time you run it, depending on how many people you have on the clock and which items they sold. I need the lookup table to be able to adjust it's size, as to not include other employees items in it's table. Every item has a specific "plu" number attached to it, so I can vlookup to find it. The closest I have gotten is using VLOOKUP(MATCH(OFFSET(COUNTIF - using COUNTIF to dictate the hight of the table, but then I'm back to my original issue, COUNTIF needs a range, which will change every time. If anyone can lead me in the right direction, it would be phenomenally appreciated. Sorry for the novel. Let me know if there are any questions or more information is needed. Thanks in advance.

 

[Robert Mika]

Hello and welcome.
To find last non empty cell in a table you can use:
=MATCH(9.99999999999999E+307,A:A)
but sample of your data will be useful to see what exactly do you need.
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[Aladin Akyurek]

> I need the lookup table to be able to adjust it's size, as to not include other employees items in it's table.

Why is it a problem that the table you refer to also has other employees?

 

[Hutchisonryan]

I need to make sure it doesn't include other employees in the table, because the item will appear on the report several times. I just want the spreadsheet to pull how many of that item that specific individual sold. For example:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
16	Clifford, Bernard	5031	Coffee	7	2.95	20.65	0	20.65	0	0.03
17		7386	Flavored\nTea	19	3.99	75.81	0	75.81	0	0.11
18		20008	Art &Spin\nDip*	1	8.99	8.99	0	8.99	0	0.01
19		20038	Rang\nRattlr6*	4	13.49	53.96	0	53.96	0	0.08
20		20040	Range\nRattlr*	2	9.99	19.98	4	15.98	20.02	0.03
21		21721	Filet\nOscarShr	3	29.98	89.94	0	89.94	0	0.13
22		21754	Top 9\nw/Shr	10	19.99	199.9	0	199.9	0	0.3
23		21759	Ribey10\nBBQShr	6	24.99	149.94	0	149.94	0	0.22
24		24511	Smothered\nTopp	3	2.49	7.47	0	7.47	0	0.01
25		27603	Chz\nCake	3	6.99	20.97	0	20.97	0	0.03
26		27636	Blkbry\nChzCake	4	6.99	27.96	0	27.96	0	0.04
27		956399	Coronarita	4	5.99	23.96	0	23.96	0	0.04
28				------------	------------	------------	------------	------------	------------	------------
29			Tot Total PPA Booster:	66	10.6	699.53	4	695.53	0.57	1.04
30
31	Hicks, Joshua	7386	Flavored\nTea	1	3.99	3.99	0	3.99	0	0.01
32		20008	Art &Spin\nDip*	1	8.99	8.99	0	8.99	0	0.01
33		20040	Range\nRattlr*	4	9.99	39.96	8	31.96	20.02	0.06
34		21754	Top 9\nw/Shr	2	19.99	39.98	0	39.98	0	0.06
35				------------	------------	------------	------------	------------	------------	------------
36			Tot Total PPA Booster:	8	11.62	92.92	8	84.92	8.61	0.14
37
38	Hegner, Donna	5031	Coffee	4	2.95	11.8	0	11.8	0	0.02
39		7386	Flavored\nTea	18	3.99	71.82	0	71.82	0	0.11
40		21721	Filet\nOscarShr	1	29.98	29.98	0	29.98	0	0.04
41		21759	Ribey10\nBBQShr	8	24.99	199.92	0	199.92	0	0.3
42		24511	Smothered\nTopp	5	2.49	12.45	0	12.45	0	0.02
43		24744	Pepper\nSauce	4	4.49	17.96	0	17.96	0	0.03
44		27605	Chzcake\nLun Ct	1	3.99	3.99	0	3.99	0	0.01
45				------------	------------	------------	------------	------------	------------	------------
46			Tot Total PPA Booster:	41	8.49	347.92	0	347.92	0	0.52
Sheet1

As you can see all of the employees lists are different sizes. This will be the case every time the report is ran, or the employee could not even show up at all. Thanks again for all the help and quick responses.

 

[Aladin Akyurek]

Would it be difficult for you to repeat employee names for each of his/her records?

 

[Hutchisonryan]

You mean by copy and pasting the employees name down column A to appear next to all of the items? Or create a page for each employee so that you can use the whole page as a table?

 

[Aladin Akyurek]

You mean by copy and pasting the employees name down column A to appear next to all of the items? Or create a page for each employee so that you can use the whole page as a table?

I mean for example copying Clifford, Bernard from row 16 to row 29...

 

[Hutchisonryan]

If there was a macro you could run easily then that would work. This is going to be distributed to all stores in the company, (to some individuals not experienced with excel), so the idea is to make it as easy, functional, and efficient as possible. It would be a report we would run daily, so the goal is to make it quick and easy for all.

 

[Hutchisonryan]

Robert Mika mentioned finding the last non empty cell in a table. Is there a way I could find the next cell with data in it (the next name on the list) possibly using match as an array formula, and offset it -1?

 

[Hutchisonryan]

I figured it out, after many many late nights of coding. Here is the answer: (must be entered as an array)

{=VLOOKUP(J11,OFFSET(INDEX('Svr Performance Measure Month 1'!A:A,MATCH(H11,'Svr Performance Measure Month 1'!A:A,0),),0,2,MATCH(1,(INDIRECT(ADDRESS((MATCH(H11,'Svr Performance Measure Month 1'!A:A,0)+1),1,,,"Svr Performance Measure Month 1")):INDIRECT(ADDRESS((MATCH(H11,'Svr Performance Measure Month 1'!A:A,0)+50),1,,,"Svr Performance Measure Month 1"))<>32)*1,0),3),3,0)}

By using match as an array to count, and then multiplying by 1 to return T/F binary, you can pop it into offset to create a table array! This also had to be coupled with the code function to turn cell text to numerical value. (Hince the <>32)