Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

Apply this formula and copy down as required. Thiw will return matching
values of 'Site A' from Sheet1 ColB ..Change the text string within the
formula to a cell reference to suit your requirement

=IF(COUNTIF(Sheet1!$A$1:$A$1000,"Site A")<ROW(A1),"",
INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$10 00="Site A",
ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


"Rechie" wrote:

When vlooup found two similar values, it always get the first one.
I have the same two values in File 1 but two have two different equivalent
values in File 2

File 1 File 2

Site A Site A=Primary
Site A Site A=Secondary

Results
Site A = Primary
Site A = Primary ? Should be = Secondary

How could I make a formula that the second value should be the also the
second value found in File 2.

Thanks.