Using Index Match across multiple worksheets

[simora]

I'm stumped getting an address into this test invoice.

Can someone please take a look at my Formula to get the address in N4.
Index/Match is what I'm trying to use, since VLOOKUP wont work for me.
See attached sample sheet.

Thanks

[Bob Phillips]

Try

=INDEX('Client-Info'!C2:K7,MATCH(G4,'Client-Info'!G2:G7,0),6)

[simora]

Thanks XLD:

I had just got this one to work
=INDEX('Client-Info'!H2:H7,MATCH(G4,'Client-Info'!G2:G7,0))
Cant fully get the hang of the final column Index ,6)
On Client-Info CrappyInvoice.xls, why doesn't ,7) work?
Is it because NO DATA is in Col A? Or is Col A index 0?

[Bob Phillips]

, 7 won't work because the 7th column of C2:K7 is I not H. , 6 works as I showed.

[simora]

xld Thanks again:

I got it. Its the xth Column of the listed range, not the worksheet itself.
Thanks for explaining that bit.

[Bob Phillips]

Exactly right.