#1   Report Post  
Hari
 
Posts: n/a
Default 2 Column Data lookup

Hi,

I have data in column A, B and C starting from row number 2.

Also, I have some data in column P and Q starting from row number 2.

The data in A and B TOGETHER constitute a Unique ID. Similarly data in
P and Q together constitute a Unique ID.

Now based on the data in P and Q I want to look up A and B and return
value from C in column R.

As of now, I use an inefficient way of building up a helper column in D
(D2 = A2 & B2) and S (S2 = P2 and Q2). Then in R2 I write the formula,
= =INDEX($C$2:$D$1000,MATCH(S2,$D$2:$D$1000,0),1)

Is there a better way of accomplishing the above (an array formula or
some mega formula which doesnt resort to inserting dummy columns etc)

Please guide me.

Regards,
Hari
India

  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Try this:

Array entered:

=INDEX($C$2:$D$1000,MATCH(P2&Q2,$A$2:$A$1000&$B$2: $B$1000,0),1)

Is it more efficient than using 2 helper columns? Maybe, maybe not! It
depends!

Biff

"Hari" wrote in message
oups.com...
Hi,

I have data in column A, B and C starting from row number 2.

Also, I have some data in column P and Q starting from row number 2.

The data in A and B TOGETHER constitute a Unique ID. Similarly data in
P and Q together constitute a Unique ID.

Now based on the data in P and Q I want to look up A and B and return
value from C in column R.

As of now, I use an inefficient way of building up a helper column in D
(D2 = A2 & B2) and S (S2 = P2 and Q2). Then in R2 I write the formula,
= =INDEX($C$2:$D$1000,MATCH(S2,$D$2:$D$1000,0),1)

Is there a better way of accomplishing the above (an array formula or
some mega formula which doesnt resort to inserting dummy columns etc)

Please guide me.

Regards,
Hari
India



  #3   Report Post  
Hari
 
Posts: n/a
Default

Hi Biff,

Thnx for the formula. Getting the idea.

Regards,
Hari
India

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
I have ad address in one column. I need to break apart the data Justin Patrick New Users to Excel 1 June 2nd 05 06:39 AM
Lookup thinks data isn't sorted Deb G Excel Worksheet Functions 5 May 26th 05 07:39 AM
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo jbsand1001 Excel Discussion (Misc queries) 1 April 28th 05 10:42 AM
How do I sort a column of data and have each data row sort accordi Oedalis Excel Discussion (Misc queries) 1 March 18th 05 12:52 AM


All times are GMT +1. The time now is 10:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"