1

I am trying to compare 3 columns of data: NPI#, month, and Year to 3 other columns with the same data looking for an exact match on all three.

If and/when all 3 match then I want to return the original NPI# and if not then return N/A.

So for example: A1:123456 B1:12 C1:2017 compared to D1:123456 E1:12 F1:2017 match so return A1:123456 I came up with this formula:

=IF(ISERROR(MATCH(A2,$B$2:$B$1126,0)),"NA",A2) which works fine for just comparing 2 different columns however, not sure how to alter this to check 3 columns against 3 others for an exact match on all 3. Any ideas? screen shot of dataI have enclosed a screen shot. So I am wanting to take each entry in columns A,B,and C and look for an exact match anywhere in columns E,F, and G.For example take the values in A2,B2, and C2 and look for an exact match (all 3 values)anywhere in columns E, F, and G. If an exact match (on all 3 values)is found then return the value in A2 or "Y". If no match then return "N". Hopefully that paints a better picture.

user941404
  • 11
  • Would be useful to see a screenshot of how your data is laid out. The formula you've pasted is referring to A2, what is in A2? because your formula is looking to match whatever is in A2 with column B, however the way you have your formula it will return NA even if it finds a match. IF syntax is IF([your_test], [value_if_true], [value_if_false]) however to do what you need you should look into Nested IF statements =IF([Something is True], [then do something], [otherwise do something else]) – angelofdev Sep 19 '18 at 23:29
  • 1
    You can use COUNTIFS funtion: =IF(COUNTIFS(A1:A1,D1,B1:B1,E1,C1:C1,F1)=1,A1,"NA") – Lee Sep 20 '18 at 08:04

1 Answers1

0

It looks like @Lee's comment was reversed from what you want since you want to return the value in the A column.

=IF(COUNTIFS($D$1:$D$9999,A1,$E$1:$E$9999,B1,$F$1:$F$9999,C1)>0,A1,"N/A")
Zusukar
  • 443