Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding text
in B1-B20 on Sheet2 to cell B2 on Sheet1.

On Sheet1:

=INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message
...
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match
of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding
text
in B1-B20 on Sheet2 to cell B2 on Sheet1.

Thanks for the code, but it did not work. My description of what I am trying
to do was inaccurate. I should have said:

I want to take the contents of cells B1 - B11025 on Sheet1, look for a match
on Sheet2 in cells A1 - A20, and return the coresponding text in B1 - B20 on
Sheet2 to cells C1 - C11025 on Sheet1.

Can this be done?

"Niek Otten" wrote:

On Sheet1:

=INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message
...
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match
of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding
text
in B1-B20 on Sheet2 to cell B2 on Sheet1.

In C1 on Sheet1 insert this formula:

=VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE)
then "fill" that formula on down to row 11025. Search Help for how to
quickly fill it. I'll try to explain one way:
With Cell C1 selected, and the formula in it, click in the Name Box -- the
Name Box is the text box looking area that will contain the current cell
address in it (C1), just above the column A label. In there, type C1:C11025
which will select all of those cells. Then use the regular menu to Edit |
Fill and choose Down. The formula will be placed into all rows you need it
to be put into.

Also, if there is no match to a value, you will get a #N/A! error in column
C. You can prevent that from showing up by changing that formula to:

=IF(ISNAVLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))," ",VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))

all of that goes on one line, not split like the editor here may do. Or you
could change it to something like:

=IF(ISNAVLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))," No Match
Found",VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))




"Mike" wrote:

Thanks for the code, but it did not work. My description of what I am trying
to do was inaccurate. I should have said:

I want to take the contents of cells B1 - B11025 on Sheet1, look for a match
on Sheet2 in cells A1 - A20, and return the coresponding text in B1 - B20 on
Sheet2 to cells C1 - C11025 on Sheet1.

Can this be done?

"Niek Otten" wrote:

On Sheet1:

=INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message
...
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match
of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding
text
in B1-B20 on Sheet2 to cell B2 on Sheet1.

Oops, I screwed up the ISNA variation of the formula, should be:

=IF(ISNA(VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE)), "",VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))

"Mike" wrote:

Thanks for the code, but it did not work. My description of what I am trying
to do was inaccurate. I should have said:

I want to take the contents of cells B1 - B11025 on Sheet1, look for a match
on Sheet2 in cells A1 - A20, and return the coresponding text in B1 - B20 on
Sheet2 to cells C1 - C11025 on Sheet1.

Can this be done?

"Niek Otten" wrote:

On Sheet1:

=INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message
...
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match
of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding
text
in B1-B20 on Sheet2 to cell B2 on Sheet1.

It's all good. Thanks for the solution and the prompt responses from both
you and Niek.

"JLatham" wrote:

Oops, I screwed up the ISNA variation of the formula, should be:

=IF(ISNA(VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE)), "",VLOOKUP(B1,'Sheet2'!$A$1:$B$20,2,FALSE))

"Mike" wrote:

Thanks for the code, but it did not work. My description of what I am trying
to do was inaccurate. I should have said:

I want to take the contents of cells B1 - B11025 on Sheet1, look for a match
on Sheet2 in cells A1 - A20, and return the coresponding text in B1 - B20 on
Sheet2 to cells C1 - C11025 on Sheet1.

Can this be done?

"Niek Otten" wrote:

On Sheet1:

=INDEX(B1:B20,MATCH(A2,Sheet2!A1:A20,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message
...
Can I take the contents ("WD4") of a cell (A2)on Sheet1, look for a match
of
the code ("WD4") on Sheet2 in cells A1-A20, and return the coresponding
text
in B1-B20 on Sheet2 to cell B2 on Sheet1.