Index Match Help Please!

[mike ryan]

Morning All,

I need a bit of help on the attached spreadsheet. Basically, I have a list of various different company addresses on sheet 1, which all have both a unique company ID (column a) and and numerical address identifier (column d). What I need to do, is transpose on to sheet 2 the corresponding address based on these 2 criteria, from the numerical address identifer noted on row 1 of sheet 2..

Sorry for the vague explanation, but hopefully the attached spreadsheet will explain better what I need to do?

Any thoughts please?

[Fotis1991]

Morning to you.

Something like this?

[acerrhod]

try this:
in sheet2 column b3:
=IFERROR(INDEX(Sheet1!$B$2:$B$8,MATCH(Sheet2!$A3&Sheet2!B$1,Sheet1!$A$2:$A$8&Sheet1!$D$2:$D$8,0),1),"")
in sheet2 column c3
=IFERROR(INDEX(Sheet1!$C$2:$C$8,MATCH(Sheet2!$A3&Sheet2!C$1,Sheet1!$A$2:$A$8&Sheet1!$D$2:$D$8,0),1),"")

you need to remove the merge cell so that it will work rather to change the Sheet B$1 every time and each column from the merge cell type the same i.e. column B & C 1 & 1, D & E 2 & 2 and so on then select column B & C and copy to Column E to K

[SDCh]

Try this:

Please Login or Register  to view this content.

Note :
* Array formula, use ctrl+shift+enter
* Copy across and down

[acerrhod]

check the attachment

[mike ryan]

Thank you all for your help, Fotis, it took a little tinkering at this end, but eventually it worked a treat(kept forgetting it's an array formula!!).

Thank you all again!

[Fotis1991]

......................