2

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.

2 Answers 2

1

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)),"")
1
  • 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, 2013 at 17:35
0

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

1
  • 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, 2012 at 21:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .