EXCEL 2003

[Mike]

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.

 

[Niek Otten]

On Sheet1:

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

 

[Mike]

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?

 

[JLatham]

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))

 

[JLatham]

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]

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