Lookup then Match and insert value from next column
How can I automate this? Please note I'm not too good with macros, if that's
what's required, but I like to try. I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. how can I have the countries in col 1 of ss 2 looked up and matched with the countries in col 1 of ss 1 and insert the value from col 2 of ss 2 into col 3 of ss 1. For example: SS1 cols 1,2: Afghanistan $50,000 SS2 cols 1,2: Afghanistan $100,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 I can't find a function or set of functions which does this. VLOOKUP and HLOOKUP do not seem to work, since I want to lookup on ss2 and Match to the same value on ss1. Likewise, MATCH does not seem to work since that Matches fine, but does not return a value in same row as the item matched. any countries where the spelling is slightly different would be marked in red or otherwise indicated that a match was not possible (probably the indication would be on ss 2 since that is where the failed matched country originated from). or is there a way that if the spelling is, for example, Afghanestan, function will find closest match and return the value in the column next to closest match? hopefully, some or all of the above is possible and can be automated. it's fine if it's a combination of 2 or more functions or a macro. appreciate your help. thanks |
Use 2 vlookup, one for each sheet but with the same lookup value and put
them next to each other -- Regards, Peo Sjoblom "Tenacity" wrote in message ... How can I automate this? Please note I'm not too good with macros, if that's what's required, but I like to try. I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. how can I have the countries in col 1 of ss 2 looked up and matched with the countries in col 1 of ss 1 and insert the value from col 2 of ss 2 into col 3 of ss 1. For example: SS1 cols 1,2: Afghanistan $50,000 SS2 cols 1,2: Afghanistan $100,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 I can't find a function or set of functions which does this. VLOOKUP and HLOOKUP do not seem to work, since I want to lookup on ss2 and Match to the same value on ss1. Likewise, MATCH does not seem to work since that Matches fine, but does not return a value in same row as the item matched. any countries where the spelling is slightly different would be marked in red or otherwise indicated that a match was not possible (probably the indication would be on ss 2 since that is where the failed matched country originated from). or is there a way that if the spelling is, for example, Afghanestan, function will find closest match and return the value in the column next to closest match? hopefully, some or all of the above is possible and can be automated. it's fine if it's a combination of 2 or more functions or a macro. appreciate your help. thanks |
I don't know why you think that Vlookup doesn't fit this situation.
Try this in Column C of Sheet1: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"Not Found",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)) This will need an *exact* spelling match in both datalists, or you'll get the "Not Found" message, which might direct you to where you can correct the spelling. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tenacity" wrote in message ... How can I automate this? Please note I'm not too good with macros, if that's what's required, but I like to try. I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. how can I have the countries in col 1 of ss 2 looked up and matched with the countries in col 1 of ss 1 and insert the value from col 2 of ss 2 into col 3 of ss 1. For example: SS1 cols 1,2: Afghanistan $50,000 SS2 cols 1,2: Afghanistan $100,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 I can't find a function or set of functions which does this. VLOOKUP and HLOOKUP do not seem to work, since I want to lookup on ss2 and Match to the same value on ss1. Likewise, MATCH does not seem to work since that Matches fine, but does not return a value in same row as the item matched. any countries where the spelling is slightly different would be marked in red or otherwise indicated that a match was not possible (probably the indication would be on ss 2 since that is where the failed matched country originated from). or is there a way that if the spelling is, for example, Afghanestan, function will find closest match and return the value in the column next to closest match? hopefully, some or all of the above is possible and can be automated. it's fine if it's a combination of 2 or more functions or a macro. appreciate your help. thanks |
Thank you RagDyer, this worked.
Pls. see my post under Excel-Programming asking about automating the conforming of cell contents. Automating conform of cell contents so they MATCH Thanks again. "RagDyer" wrote: I don't know why you think that Vlookup doesn't fit this situation. Try this in Column C of Sheet1: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"Not Found",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)) This will need an *exact* spelling match in both datalists, or you'll get the "Not Found" message, which might direct you to where you can correct the spelling. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tenacity" wrote in message ... How can I automate this? Please note I'm not too good with macros, if that's what's required, but I like to try. I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. how can I have the countries in col 1 of ss 2 looked up and matched with the countries in col 1 of ss 1 and insert the value from col 2 of ss 2 into col 3 of ss 1. For example: SS1 cols 1,2: Afghanistan $50,000 SS2 cols 1,2: Afghanistan $100,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 I can't find a function or set of functions which does this. VLOOKUP and HLOOKUP do not seem to work, since I want to lookup on ss2 and Match to the same value on ss1. Likewise, MATCH does not seem to work since that Matches fine, but does not return a value in same row as the item matched. any countries where the spelling is slightly different would be marked in red or otherwise indicated that a match was not possible (probably the indication would be on ss 2 since that is where the failed matched country originated from). or is there a way that if the spelling is, for example, Afghanestan, function will find closest match and return the value in the column next to closest match? hopefully, some or all of the above is possible and can be automated. it's fine if it's a combination of 2 or more functions or a macro. appreciate your help. thanks "RagDyer" wrote: I don't know why you think that Vlookup doesn't fit this situation. Try this in Column C of Sheet1: =IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"Not Found",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)) This will need an *exact* spelling match in both datalists, or you'll get the "Not Found" message, which might direct you to where you can correct the spelling. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tenacity" wrote in message ... How can I automate this? Please note I'm not too good with macros, if that's what's required, but I like to try. I have a list of countries in column 1 with corresponding values in column 2. I have a second spreadsheet with the same countries (usually spelled the same way) in column 1 and a new set of values in column 2. how can I have the countries in col 1 of ss 2 looked up and matched with the countries in col 1 of ss 1 and insert the value from col 2 of ss 2 into col 3 of ss 1. For example: SS1 cols 1,2: Afghanistan $50,000 SS2 cols 1,2: Afghanistan $100,000 I would like to automatically have SS1 result in the following in cols. 1,2,3: Afghanistan $50,000 $100,000 I can't find a function or set of functions which does this. VLOOKUP and HLOOKUP do not seem to work, since I want to lookup on ss2 and Match to the same value on ss1. Likewise, MATCH does not seem to work since that Matches fine, but does not return a value in same row as the item matched. any countries where the spelling is slightly different would be marked in red or otherwise indicated that a match was not possible (probably the indication would be on ss 2 since that is where the failed matched country originated from). or is there a way that if the spelling is, for example, Afghanestan, function will find closest match and return the value in the column next to closest match? hopefully, some or all of the above is possible and can be automated. it's fine if it's a combination of 2 or more functions or a macro. appreciate your help. thanks |
All times are GMT +1. The time now is 02:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com