Search for Referenced Data

[Frank J]

I'm trying to search for data using a referenced cell and on a different worksheet return the value in the row below in the proper column. On the first worksheet the referenced cell's value is 107, I want to look at the second worksheet and find 107, look across the columns to a specific column and then look down at the row below to find the data in that cell. In the example below the answer would be 79,300. [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]107
[/TD]
[TD]BJ
[/TD]
[TD]41,000
[/TD]
[TD]25,000
[/TD]
[TD]8,700
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3,700
[/TD]
[TD]800
[/TD]
[TD]100
[/TD]
[TD]79,300
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Frank J]

What can I use to solve my issue?

 

[Andrew Poulsom]

Maybe:

=INDEX($B$9:$K$700,MATCH($R9,$B$9:$B$700,FALSE)+1,9)

 

[Frank J]

It works on the first table but when I copy it down it goes wrong, I'll work on it but I'm not seeing where it is wrong it looks at everything in the same manner just one item is different that's the table ID number

 

[Andrew Poulsom]

Can you post some sample data that demonstrates it going wrong?

 

[Frank J]

[TABLE="width: 1255"]
<tbody>[TR]
[TD]Game Type
[/TD]
[TD]Table ID
[/TD]
[TD]$25,000
[/TD]
[TD]$5,000
[/TD]
[TD]$1,000
[/TD]
[TD]$500
[/TD]
[TD]$100
[/TD]
[TD]$25
[/TD]
[TD]$5
[/TD]
[TD]$1
[/TD]
[TD]$0.50
[/TD]
[TD]$0.25
[/TD]
[TD] Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TC
[/TD]
[TD]101
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$41,000.00
[/TD]
[TD="align: right"]$22,500.00
[/TD]
[TD="align: right"]$9,100.00
[/TD]
[TD="align: right"]$3,500.00
[/TD]
[TD="align: right"]$600.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$69,800.00
[/TD]
[TD="align: right"]$76,800.00
[/TD]
[/TR]
[TR]
[TD]UT
[/TD]
[TD]102
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,100.00
[/TD]
[TD="align: right"]$1,000.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$71,700.00
[/TD]
[TD="align: right"]$78,200.00
[/TD]
[/TR]
[TR]
[TD]SD
[/TD]
[TD]103
[/TD]
[TD="align: right"]$900.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$600.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$69,200.00
[/TD]
[TD="align: right"]$1,700.00
[/TD]
[/TR]
[TR]
[TD]SR
[/TD]
[TD]104
[/TD]
[TD="align: right"]$777.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$43,000.00
[/TD]
[TD="align: right"]$14,500.00
[/TD]
[TD="align: right"]$10,700.00
[/TD]
[TD="align: right"]$3,600.00
[/TD]
[TD="align: right"]$1,100.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$393,500.00
[/TD]
[TD="align: right"]$73,777.00
[/TD]
[/TR]
[TR]
[TD]RW
[/TD]
[TD]105
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,600.00
[/TD]
[TD="align: right"]$300.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,200.00
[/TD]
[TD="align: right"]$73,000.00
[/TD]
[/TR]
[TR]
[TD]DD
[/TD]
[TD]106
[/TD]
[TD="align: right"]$700.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$400.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$59,100.00
[/TD]
[TD="align: right"]$1,300.00
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD]107
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$46,000.00
[/TD]
[TD="align: right"]$19,000.00
[/TD]
[TD="align: right"]$8,400.00
[/TD]
[TD="align: right"]$3,200.00
[/TD]
[TD="align: right"]$900.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,100.00
[/TD]
[TD="align: right"]$77,600.00
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD]108
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,700.00
[/TD]
[TD="align: right"]$700.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,600.00
[/TD]
[TD="align: right"]$78,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$885,200.00
[/TD]
[TD="align: right"]$460,877.00
[/TD]
[/TR]
</tbody>[/TABLE]
Here you can see the data that was pulled into this array using the formula, it lists the tables, their IDs, chip denominations (they should be in proper units shown in the headers) and on the last and next to last are totals. The totals should be exactly equal, the last column is adding up all the denominations to the left and comparing it's total to the cell next to it on the left you see they don't match. The total for all tables don't match either.

 

[Andrew Poulsom]

I can't really help without seeing the source data.

 

[Frank J]

[TABLE="class: grid, width: 1108"]
<tbody>[TR]
[TD]C
[/TD]
[TD]
[/TD]
[TD]CP
[/TD]
[TD]
[/TD]
[TD]_5
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]PAGE 1
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]FS
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CH
[/TD]
[TD]
[/TD]
[TD]CH
[/TD]
[TD]
[/TD]
[TD]SD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--
[/TD]
[TD]-------
[/TD]
[TD]--
[/TD]
[TD]---------
[/TD]
[TD]---------------------------
[/TD]
[TD]---------------------------------
[/TD]
[TD]-------------
[/TD]
[TD]-------------------------------------
[/TD]
[TD]-----------------
[/TD]
[TD]--------------------
[/TD]
[TD]-------------------------------
[/TD]
[/TR]
[TR]
[TD]GM
[/TD]
[TD]E/TBL/S
[/TD]
[TD]GM
[/TD]
[TD]T
[/TD]
[TD="align: right"]$25,000.00
[/TD]
[TD="align: right"]$5,000.00
[/TD]
[TD="align: right"]$1,000.00
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]$200.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$25.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$5.00
[/TD]
[TD="align: right"]$1.00
[/TD]
[TD="align: right"]$0.50
[/TD]
[TD="align: right"]$0.25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--
[/TD]
[TD]-------
[/TD]
[TD]--
[/TD]
[TD]---------
[/TD]
[TD]---------------------------
[/TD]
[TD]---------------------------------
[/TD]
[TD]-------------
[/TD]
[TD]-------------------------------------
[/TD]
[TD]-----------------
[/TD]
[TD]--------------------
[/TD]
[TD]-------------------------------
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]107
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41,000.00
[/TD]
[TD="align: right"]22,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,100.00
[/TD]
[TD="align: right"]3,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]900
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]108
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD="align: right"]777
[/TD]
[TD][/TD]
[TD="align: right"]43,000.00
[/TD]
[TD="align: right"]14,500.00
[/TD]
[TD][/TD]
[TD="align: right"]10,700.00
[/TD]
[TD="align: right"]3,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]72,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]301
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46,000.00
[/TD]
[TD="align: right"]19,000.00
[/TD]
[TD][/TD]
[TD="align: right"]8,400.00
[/TD]
[TD="align: right"]3,200.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77,700.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]302
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30,000.00
[/TD]
[TD="align: right"]22,000.00
[/TD]
[TD][/TD]
[TD="align: right"]8,100.00
[/TD]
[TD="align: right"]4,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]65,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,200.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]303
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44,000.00
[/TD]
[TD="align: right"]20,500.00
[/TD]
[TD][/TD]
[TD="align: right"]8,700.00
[/TD]
[TD="align: right"]4,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78,400.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]304
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42,000.00
[/TD]
[TD="align: right"]16,500.00
[/TD]
[TD][/TD]
[TD="align: right"]8,900.00
[/TD]
[TD="align: right"]3,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]71,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]308
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46,000.00
[/TD]
[TD="align: right"]22,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,700.00
[/TD]
[TD="align: right"]2,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]81,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]309
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43,000.00
[/TD]
[TD="align: right"]18,000.00
[/TD]
[TD][/TD]
[TD="align: right"]10,400.00
[/TD]
[TD="align: right"]6,300.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]407
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39,000.00
[/TD]
[TD="align: right"]30,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,000.00
[/TD]
[TD="align: right"]3,925.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,060.00
[/TD]
[TD="align: right"]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]501
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18,500.00
[/TD]
[TD][/TD]
[TD="align: right"]6,100.00
[/TD]
[TD="align: right"]2,650.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,475.00
[/TD]
[TD="align: right"]75
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]502
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]11/15/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19,000.00
[/TD]
[TD][/TD]
[TD="align: right"]10,400.00
[/TD]
[TD="align: right"]3,900.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,110.00
[/TD]
[TD="align: right"]90
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]503
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]11/15/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21,000.00
[/TD]
[TD][/TD]
[TD="align: right"]9,500.00
[/TD]
[TD="align: right"]4,525.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,040.00
[/TD]
[TD="align: right"]35
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]504
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]10/31/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21,500.00
[/TD]
[TD][/TD]
[TD="align: right"]7,700.00
[/TD]
[TD="align: right"]3,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,230.00
[/TD]
[TD="align: right"]70
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]511
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,400.00
[/TD]
[TD="align: right"]2,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28,000.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,200.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

The data for the smaller denominations is in the third row rather than the second row:

Excel 2010
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Game Type	Table ID	$25,000	$5,000	$1,000	$500	$200	$100	$25	$5	$1	$0.50	$0.25	Total	Check
2	TC	107	0	0	41000	22500	0	9100	3500	900	100	0	0	77100	77100
Sheet1

Cell Formulas
Range	Formula
O2	=INDEX(E$9:E$50,MATCH($N2,$B$9:$B$50,FALSE))
P2	=INDEX(F$9:F$50,MATCH($N2,$B$9:$B$50,FALSE))
Q2	=INDEX(G$9:G$50,MATCH($N2,$B$9:$B$50,FALSE))
R2	=INDEX(H$9:H$50,MATCH($N2,$B$9:$B$50,FALSE))
S2	=INDEX(I$9:I$50,MATCH($N2,$B$9:$B$50,FALSE))
T2	=INDEX(J$9:J$50,MATCH($N2,$B$9:$B$50,FALSE))
U2	=INDEX(K$9:K$50,MATCH($N2,$B$9:$B$50,FALSE))
V2	=INDEX(E$9:E$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
W2	=INDEX(F$9:F$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
X2	=INDEX(G$9:G$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
Y2	=INDEX(H$9:H$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
Z2	=SUM(O2:Y2)
AA2	=INDEX(K$9:K$50,MATCH($N2,$B$9:$B$50,FALSE)+1)

 

[Frank J]

I've tried this formula and it returns #N/A Did you notice that in the raw data the tables are listed by game type in that grouping and I'm regrouping according to location on the casino floor and this will cause some data to change location on the spreadsheet from time to time.

 

[Andrew Poulsom]

Which formula? I copied the data you posted and pasted it into A1. You can see that the formulas work from the table I posted.