Match values in two columns and return value using

Question

I have a worksheet (called FINAL) with similar data as below:

    A        B      C 
  Year   Month  Births  
  1880     1     530  
  1880     2     456  
  1880     3     234     
  1890     1     163     
  1890     2     123     
  1890     3     125 

Second spreadsheet:

   A       B      C      D
  Year   Month  Births  
  1880     1        
  1890     2               
  1890     3

I wish to look for mach for a value from column A in sheet 2 e.g. 1880 from sheet 2, in sheet 1 column A then return a value in D when it meets a specific month criteria e.g. 1880(A) and 1(B) then return 530 in Column D

This formula I wrote does not give me anything (gives back NA)

=IF(IF(A2=Final!B12,TRUE,FALSE),TRUE,VLOOKUP(Final!A2,Final!B12:C3532,2,FALSE))

To me the logic is, if A2=B2 is TRUE then outer if gets a TRUE and if its true then do the Vlookup and return the value in second column else false) but it doesn't work

Answer 1

You need to put the logic in the lookup.

=INDEX(Final!$C$1:$C$500(MATCH(1,IF(A2=Final!$A$1:$A$500,IF(B2=Final!$B$1:$B$500,1,0),0),0))

This is an array formula. It needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter.