Find Data in one array and match the position in another array

[zeeman2]

Hello,

I have been working with a large amount of data and came across this problem. I am trying to get a formula that will find the correct data in an array and then find the data in another array for the matching position. Attached is an example.

For instance the number "8" is in position A6 in the first array and the data in the same position in the second array is "10"

Excelproblem.jpg

I have combined and tried several different formulas, but so far nothing has done it correctly.

Thanks

[FDibbins]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

[zeeman2]

Book2.xlsx

Maybe this will help.

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E	F
1	----	----	----	----	Data	New Data
2	10	19	50		8	10
3	15	11	99		10	99
4	65	24	128		11	46
5	45	74	46		15	11
6	8	36	21		19	74
7					21	50
8					24	128
9					36	8
10	99	74	65		45	45
11	11	46	19		46	36
12	24	128	21		50	65
13	45	15	36		65	24
14	10	8	50		74	15
15					99	19
16					128	21

This array formula** entered in F2 and copied down:

=OFFSET(A$10,MAX(IF(A$2:C$6=E2,ROW(A$2:C$6)-ROW(A$2))),MAX(IF(A$2:C$6=E2,COLUMN(A$2:C$6)-COLUMN(A2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

There must not be any duplicate entries in the top table.

[zeeman2]

That worked great! I also see where I made my mistakes. Thanks.

[Tony Valko]

You're welcome. Thanks for the feedback!