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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>