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.

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.

Hi Jacob,

I have various sites in File A (Site A, site B, Site C and so on), not only
Site A.
with the same scenario, two same sites with diff corresponding desc in File 2.
Is this formula will likewise do?




"Jacob Skaria" wrote:

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.

With data as below in Sheet1; the formula would return the 1st matching value
in ColB for siteA,,if copied down the formula will return all values
corresponding to SiteA

Col A Col B
SiteA a
SiteB b
SiteA c
SiteC d
SiteA e

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


"Rechie" wrote:

Hi Jacob,

I have various sites in File A (Site A, site B, Site C and so on), not only
Site A.
with the same scenario, two same sites with diff corresponding desc in File 2.
Is this formula will likewise do?




"Jacob Skaria" wrote:

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.

Hi Jacob,

It seems not working in my file. Maybe we could make more simplier.
There is always 2 same sites in workbook 1 and with two different
description (for all sites). In Workbook2, the first site should always get
the first description as shown in Workbook1 and the same 2nd site will get
the 2nd desc, as below:

Workbook1
ColumA Column B
Site A Primary
Site A Secondary
Site B Primary
Site B Secondary
608 Primary
608 Secondary

Workbook2
Column A Column B
Site A blank (formula to get above data: Primary)
Site A blank (formula to get above data: Secondary)
Site B blank
Site B blank
608 blank
608 blank

Note also that some sites are numeric, e.g 608

"Jacob Skaria" wrote:

With data as below in Sheet1; the formula would return the 1st matching value
in ColB for siteA,,if copied down the formula will return all values
corresponding to SiteA

Col A Col B
SiteA a
SiteB b
SiteA c
SiteC d
SiteA e

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


"Rechie" wrote:

Hi Jacob,

I have various sites in File A (Site A, site B, Site C and so on), not only
Site A.
with the same scenario, two same sites with diff corresponding desc in File 2.
Is this formula will likewise do?




"Jacob Skaria" wrote:

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.

Did you enter the formula using CTRL+SHIFT+ENTER..as mentioned in my original
post.

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


"Rechie" wrote:

Hi Jacob,

It seems not working in my file. Maybe we could make more simplier.
There is always 2 same sites in workbook 1 and with two different
description (for all sites). In Workbook2, the first site should always get
the first description as shown in Workbook1 and the same 2nd site will get
the 2nd desc, as below:

Workbook1
ColumA Column B
Site A Primary
Site A Secondary
Site B Primary
Site B Secondary
608 Primary
608 Secondary

Workbook2
Column A Column B
Site A blank (formula to get above data: Primary)
Site A blank (formula to get above data: Secondary)
Site B blank
Site B blank
608 blank
608 blank

Note also that some sites are numeric, e.g 608

"Jacob Skaria" wrote:

With data as below in Sheet1; the formula would return the 1st matching value
in ColB for siteA,,if copied down the formula will return all values
corresponding to SiteA

Col A Col B
SiteA a
SiteB b
SiteA c
SiteC d
SiteA e

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


"Rechie" wrote:

Hi Jacob,

I have various sites in File A (Site A, site B, Site C and so on), not only
Site A.
with the same scenario, two same sites with diff corresponding desc in File 2.
Is this formula will likewise do?




"Jacob Skaria" wrote:

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.

Yes, and curly braces at both ends like "{=<formula}" are also appearing.
Only the first two lines showed the results (site A)


"Jacob Skaria" wrote:

Did you enter the formula using CTRL+SHIFT+ENTER..as mentioned in my original
post.

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


"Rechie" wrote:

Hi Jacob,

It seems not working in my file. Maybe we could make more simplier.
There is always 2 same sites in workbook 1 and with two different
description (for all sites). In Workbook2, the first site should always get
the first description as shown in Workbook1 and the same 2nd site will get
the 2nd desc, as below:

Workbook1
ColumA Column B
Site A Primary
Site A Secondary
Site B Primary
Site B Secondary
608 Primary
608 Secondary

Workbook2
Column A Column B
Site A blank (formula to get above data: Primary)
Site A blank (formula to get above data: Secondary)
Site B blank
Site B blank
608 blank
608 blank

Note also that some sites are numeric, e.g 608

"Jacob Skaria" wrote:

With data as below in Sheet1; the formula would return the 1st matching value
in ColB for siteA,,if copied down the formula will return all values
corresponding to SiteA

Col A Col B
SiteA a
SiteB b
SiteA c
SiteC d
SiteA e

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


"Rechie" wrote:

Hi Jacob,

I have various sites in File A (Site A, site B, Site C and so on), not only
Site A.
with the same scenario, two same sites with diff corresponding desc in File 2.
Is this formula will likewise do?




"Jacob Skaria" wrote:

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.