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 am a bit confused with lookup/match and the lookup wizard in excel 07 does not seem to allow multiple sheets properly.

What I need is for cell B2:BXXXX on Sheet 2 to run the following and display the answer compare Cell A2:AXXXX on sheet 2 to the values on D2:D30000 on sheet 3, finding the exact match only if exact match is found, return the entire contents of Cell L2:L30000 on sheet 3 where the row is the matching row from D2:D30000 If no match is found, return #N/A is fine

example

Sheet 2
90336-N 32(formula is here)
90336-R #N/A (formula is copy down'ed this entire column)

Sheet 3

D   L
90336-N 32
90336   12
90336-Q 8

any help? - the entire sheet has alot of financial data as well as company databases that I cannot post, the data above is just simply some "fluff"

share|improve this question

migrated from stackoverflow.com Nov 19 '12 at 15:47

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 1 down vote accepted

You also want to add IFERROR to the vlookup command. This will allow you to search for non-matches more easily and even designate them as something for example: =IFERROR(VLOOKUP(B2,Sheet3!D$2:L$30000,9,FALSE),"CHECK")

share|improve this answer

The most common approach is to use VLOOKUP. In B2 on sheet2 use this formula

=VLOOKUP(B2,Sheet3!D$2:L$30000,9,FALSE)

You can then copy that formula down the column

The 9 indicates the 9th column of the range Sheet3!D$2:L$30000, because that's the column from which you want to return a value (the match is always done with the first column of that range). VLOOKUP with FALSE as final parameter only returns exact matches or #N/A (although it's not case-sensitive)

share|improve this answer

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.