Lookup then Match and insert value from next column

T

Tenacity

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
 
P

Peo Sjoblom

Use 2 vlookup, one for each sheet but with the same lookup value and put
them next to each other
 
R

RagDyer

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!
==============================================

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
 
T

Tenacity

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top