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.

107	BJ	41,000	25,000	8,700
		3,700	800	100	79,300

<tbody>
</tbody>

 

[Andrew Poulsom]

Does this help you?

Excel 2010
	A	B	C	D	E	F	G	H	I
1	107	BJ	41,000	25,000	8,700			107	79300
2			3,700	800	100	79,300
Sheet1

Cell Formulas
Range	Formula
I1	=INDEX(A1:F2,MATCH(H1,A1:A2,FALSE)+1,6)

 

[Frank J]

Will this work on a sheet where I'll need 71 of these spread out over 11 columns and 500 rows?

 

[Andrew Poulsom]

Will this work on a sheet where I'll need 71 of these spread out over 11 columns and 500 rows?

I don't see why not. Adjust the range references to suit.

 

[Frank J]

I'm getting #N/A when there is a blank cell as a result of the formula, how can I get it to return a zero or blank in this instance?

 

[Andrew Poulsom]

What formula and with what data?

 

[Frank J]

I'm at work so I can't use all the tools to upload the workbook but I'm trying to format data imported from an SQL query that's now in excel and is all over the sheet since the import was from text to excel. I have 71 gaming tables with data for each on 3 different rows across 10 columns. It's not sorted and can't really be so I'm trying use the ID numbers which are unique to each as a focal point to organize the data. I'm trying to pull it across the sheet to cells to the right. The data is stored in cells A1 to K700 so I'm pulling the data to the right starting in column Q. The desire is to have it sorted by table ID number vertically with the inventory of each denomination listed across in columns with a total to the far right.
I took your formula and adjusted for the proper cells making the Index portion look in A1:K700 and the Match to use the ID numbers as a reference point and using the same array adjusting for rows and columns where each denomination is stored.

 

[Andrew Poulsom]

So what formula are you actually using that returns #N/A?

 

[Frank J]

=INDEX($B$9:$K$700,MATCH($R9,$B$9:$K$700,FALSE)*0,3) for the data on the same row and =INDEX($B$9:$K$700,MATCH($R9,$B$9:$K$700,FALSE)+2,3) for data 2 rows below and =INDEX($B$9:$K$700,MATCH($R9,$B$9:$K$700,FALSE)+1,9) for data 1 row below. Adjusting for different columns

 

[Andrew Poulsom]

MATCH expects a single column or row.