Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
Vlookup found two similar values
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. |
All times are GMT +1. The time now is 03:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com