Lookup for concatenated data

[Hari]

Hi,

Probably I have asked this before, but I might have missed it.

I have raw/source data in Column A and C (together A and C constitute
Unique ID) of sheet1 extending up to 1000 columns.

I also have some data in column Z (unique ID) and this data maps to
combination of data in A and C.

Presently in order to do a vlookup of Z with A2&C2, I have to create a
helper column D in which D2 = A2&D2 and use this helper column as the
TABLE ARRAY. Is there a way in wich we could perfom a lookup of Col Z
with A and C (and return value in Column E), without creating a helper
column. Probably some array formula could do this.

Please guide me for the same.

Regards,
HP
India

 

[Bob Phillips]

=INDEX(E:E,MATCH(A2&C2,Z:Z,0))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Hari]

Bob,

Thanks for the response. Im sorry if I havent made my issue not clear.

In my case the Table array in Lookup formula is to be concatenated (and
not the lookup value).

For example

If Table array has data from A2 through E500, then Col A and Col C
together constitute unique ID.

Also, I have some unique ID's from Z2 through Z10. For each of these 9
Id's I want to do a lookup with Table array and return data from column
E.

So my "incorrect" formula would look something like this.

AA2 = Index(E2:E500,Match(Z2,A2:A500&C2:C500),1)

Basically I want to concatenate the first and third column of my Table
array/Lookup Array.

Regards,
HP
India