Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a 2 Sheets x and y as follows:

x
____________________________________________
State    | 1         | 2         | 3
ME       |           |           |         
VA       |           |           |



y
__________________________________
State    | Type     |   Date  
VA       |  1       | 2/12/2012
VA       |  3       | 1/15/2012
ME       |  2       | 8/1/2011
VA       |  2       | 2/2/2012
ME       |  1       | 1/4/2011

I need a formula that can find and match the values in columns A and B of sheet y against B1 and A2 of sheet x and put the value of the corresponding date from sheet y into the correct column on sheet x

The issue is that most excel 2d arrays require unique values in the first lookup column (in this case 'y'!$A:$A) .

Is this possible?

I have tried:

=IF(ISNA(INDEX('y'!$C:$C,MATCH(1,($A2='y'!$A:$A)*(B$2='y'!$B:$B),0))),"",INDEX('y'!$C:$C,MATCH(1,($A1='y'!$A:$A)*(B$2='y'!$B:$B),0)))

To no avail.

share|improve this question
add comment

migrated from stackoverflow.com Oct 26 '12 at 12:47

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

use the following formula in your destination cells, entered as an array formula with Ctrl+Shift+Enter

=index(sheety!$C:$C,match($A2&B$2,sheety!$A:$A&Sheety!$B:$B,0))
share|improve this answer
    
This is great, Thanks! (I have 2 minutes left to mark as answer) –  PRNDL Development Studios Oct 25 '12 at 19:31
    
+1 sheety formula. –  brettdj Oct 26 '12 at 9:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.