Complex lookup

[tonkerthomas]

Good morning everybody.

I have a spreadsheet which is made up of six identical, adjacent blocks of data, each of twenty columns or so. The structure of each block of data is identical. Within those six blocks of data, I need to be able to find a value (which can only appear once), and look up the value which is next to it. I feel sure this isn't that hard, but having not used Excel for a few years, and with a new version (Office 365) to work with, I'm struggling to do things which I might once have done without thinking. It's a terrible thing to get old.

Can anybody help me, please?

 

[oldbrewer]

			col D
	row 7		7	8	9			21	22	23		31	32	33
			3	4	5			24	25	26		41	99	55
			8	6	2			27	28	29		66	67	68
				find the 99 hidden somewhere in the 3 tables above and return the number to the right of it
			7	#N/A
			8	12	8		the 8 is the row number containing the 99	and is in cell F20
			9	#N/A
			55
			obtained by
			=OFFSET($A$1,$F$20-1,E20+2)
			copyright
			bob aged 73

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

I am old and make typos

			col D
	row 7		7	8	9			21	22	23		31	32	33
			3	4	5			24	25	26		41	99	55
			8	6	2			27	28	29		66	67	68
				find the 99 hidden somewhere in the 3 tables above and return the number to the right of it
			7	#N/A
			8	12	8		the 8 is the row number containing the 99 and is in cell F20
			9	#N/A
			55
			obtained by
			=OFFSET($A$1,$F$20-1,E20+2)
			copyright
			bob aged 73

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[tonkerthomas]

I am old and make typos

			col D
	row 7		7	8	9			21	22	23		31	32	33
			3	4	5			24	25	26		41	99	55
			8	6	2			27	28	29		66	67	68
				find the 99 hidden somewhere in the 3 tables above and return the number to the right of it
			7	#N/A
			8	12	8		the 8 is the row number containing the 99 and is in cell F20
			9	#N/A
			55
			obtained by
			=OFFSET($A$1,$F$20-1,E20+2)
			copyright
			bob aged 73

<tbody>
</tbody>

Thank you, Bob, your time is very much appreciated. Unless I've misunderstood this, though, OFFSET only works if I already know where the value I'm looking for is - and I don't. The data is not static, it will change, and then I need to be able to not only look up the value to the right of my target, but to be able to find my target (in your example, the 99) in the first place. Does that make sense? Have I got the wrong end of the stick?

Jeff

 

[oldbrewer]

Jeff - my formula did not know where the 99 was - the 12 was found by =MATCH(99,C8:O8,0) - this was applied to every row, so returned 2 errors and the 12 meaning the 12th cell in C8 to O8

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	8	6	6	3	4	8
2	1	5	9	8	4	10
3	6	8	1	6	5	3
4	9	9	99	10	10	3
5	2	10	7	1	6	2
6	6	4	4	10	11	1
7	5	5	8	4	7	3
8
9	99
10	10
Quote

Cell Formulas
Range	Formula
A10	=SUMPRODUCT(--(A1:F7=A9),(B1:G7))

 

[oldbrewer]

Fluff - can you explain your solution please - why B1:G7 in last part ?

Bob

 

[Fluff]

It's effectively offsetting the range by 1 column, so that the returned value is 1 column to the right of the "found" value

 

[oldbrewer]

thanks

 

[Fluff]

You're welcome