Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.

Sheet 1:

Column A | Column B | Column C | Column D

_ Name _ | _ Date ._ | _ Cost .._ | _ Code _

Sheet 2:

Column A | Column B

_ Code _ | _ CodeDesc

What I would like to do in Column E of Sheet 1 is test the code in Column D against Column A in Sheet 2.

For example: if Sheet1!D4 equals anything in the range of Sheet2!A:A - say A37, return B37 as the value in Sheet1!E4.

I had originally wanted to do an if function for it, but to no avail. VLookup won't work, as this sheet cannot be sorted by the codes.

My original thought: =IF(B4 = Sheet2!A2:A162,Sheet2!B2:B162,)

I knew it wouldn't work, so please don't point out the idiocy of it. It was just where my head went at first and apparently this close to Christmas my working brain has taken a leave of absence. And a leave of sense.

share|improve this question
add comment

2 Answers

VLOOKUP will work fine as long as you set the last parameter to FALSE.  For example, set E4 to =VLOOKUP(D4, Sheet2!A:B, 2, FALSE).

share|improve this answer
    
Thanks. I'll do that next time. I kind of cheated after I wasted entirely too much time on it and changed the Crystal Report I was exporting the data from to include the code description, but I'm going to give that a shot anyways to make sure it works and then put it in my reference binder. –  Angela Dec 20 '12 at 21:31
add comment

This formula should work. If there are any issues then try using named ranges.

=IFERROR(INDEX('Sheet2'!$B$2:$B$162,MATCH('Sheet1'!$D2,'Sheet2'!$A$2:$A$162,0)),"")
share|improve this answer
    
Thanks! I'm putting this one into my reference binder as well, because I've run into a few reports that need it. :) –  Angela Jan 3 '13 at 17:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.