I’m trying to create a formula in Excel and I am having problems with it - think from previous posts it is a vlookup but can't get this to work as I hoped it would.

In sheet 1 (called raw data (2)) I have lots of cells with data.
In sheet 2 I have some of this data and blank cells. I need to look at sheet 1 column B and see if it matches any cells in Sheet 2 column B. If it does match then I need to take results from sheet 1 columns G to AA and input these into sheet 2 columns E to Y.

I created a vlookup with the formula =VLOOKUP(B3,'RawData (2)'!B2:B6659,7,FALSE) to try to enter the first result into sheet 2 column E. This gives me a N/A result despite my knowing that cell B3 does have a matching result in sheet 1.


Are you sure that it’s giving you #N/A and not #REF?  Because #REF is the expected result of the fact that your “table_array” (the second argument to VLOOKUP) doesn’t include the data that you want to retrieve, e.g.,

=VLOOKUP(B3, 'RawData (2)'!B2:AA6659, 7, FALSE)

Also, if you want to get the value from column G, the third argment should be 6, as this is counted relative to the B column (i.e., column B is number 1).  Also, you’ll want to make the row and column numbers absolute ($B3, $B$2, and $AA$6659) before you try to fill Sheet2 with this formula.

Did you deliberately mis-match the row numbers (2 ≠ 3)?


Your lookup range is just in one column (B2:B6659) but you've asked it to get the answer from the 7th column across.


  • External links can become unavailable, in which case your post would only hint at an answer, rather than providing a solution. Links should be used for attribution and a source of further information, but the essential points should be included in the answer. If your intent is just to provide a helpful hint to point the person in the right direction, that should be done as a comment, which you need a little more rep for. – fixer1234 Feb 17 '15 at 5:26

Try this formula:

 =VLOOKUP(B3,'RawData (2)'!B2:AA6659,7,FALSE)

I believe your issue is that you set your array as only the B column, when it should span both the lookup column and the data columns you want to draw from. The lookup column, of course, needs to be on the far left of the array.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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