How to find exact matches from two list & columns?

user_12170313 0
Viewed 116

What formula would pull exact matches comparing column A & B from list 1 to list 2?

enter image description here

List 1 stays constant & list 2 floats with add-inns.

  • if there's any additional info a string would say ADD if true returns value*

This is my current formula => =IF(COUNTIFS(A:A,$D2,A:A,$E$2),$E$2,"ADD") I attempted VLOOKUP formula, but column A has duplicates with different values in list.

countif vlookup vba excel
2 Answers

Return an Occurrence of a Value

  • For the n-th occurrence of a value in a column, returns the associated (in the same row) value in a second (value) column of the n-th occurrence of the value in a third (lookup) column.

  • Try the following array formula (AltShift+Enter) in cell E2 and copy down.

=IFERROR(INDEX(B$2:B$7,SMALL(IF(A$2:A$7=D2,ROW(A$2:A$7)-ROW(INDEX(A$2:A$7,1,1))+1),COUNTIF(D$2:D2,D2))),"")

Formula Evaluations for Cell E2

IFERROR                                 "b"
INDEX                                   "b"
B$2:B$7                                 {"b",1,2,5,10,20}
SMALL                                   1
IF                                      {1,2,3,FALSE,FALSE,FALSE}
A$2:A$7=D2                              {TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
A$2:A$7                                 {"a","a","a","b","b","c"}
D2                                      "a"
ROW(A$2:A$7)-ROW(INDEX(A$2:A$7;1;1))+1  {1,2,3,4,5,6}
ROW(A$2:A$7)-ROW(INDEX(A$2:A$7;1;1))    {0,1,2,3,4,5}
ROW(A$2:A$7)                            {2,3,4,5,6,7}
ROW(INDEX(A$2:A$7;1;1))+1               {3}
ROW(INDEX(A$2:A$7;1;1))                 {2}
INDEX(A$2:A$7;1;1)                      "a"
COUNTIF(D$2:D2;D2)                      1
user_98140690

A potential solution would be the following:

On the first row, you can use a simple VLOOKUP as below:

First row formular

=VLOOKUP(D3;A3:B6;2;FALSE)

On the second row and so on, you can use the following formula (attention on the details of the $ blocks you must apply):

enter image description here

=IF(VLOOKUP($D4;$A:$B;2;FALSE)=IFERROR(VLOOKUP($D4;$D$2:$E3;2;FALSE);0);SUMIF($A:$A;$D4;$B:$B)-SUMIF($D$2:$D3;$D4;$E$2:$E3);VLOOKUP($D4;$A:$B;2;FALSE))
user_157244600
Related