VLookup is giving me #N/A error, help with data or formula?

**ladygogo** · 2012-10-21, 08:39 PM

I've searched the web and can't find a solution to my problem with excel mac 2011.

My lookup_values are a combination of both text and numbers (they are manufacturer's part number in Column C).
This is my formula =VLOOKUP(C4:C204,Sheet1!A1:AV86,34,0). I need Delivery Time which corresponds to the other worksheet in Column AH (C34)

I believe the problem is with my data rather than my formula but not sure how to solve it. there's no trailing space, i cleared formats, etc. But I'm still getting the dreaded #N/A value.
Ive checked if the values match with =B2=B2=True

Hope somebody has a solution for me!! I've attached the sample data

**tommyt61** · 2012-10-21, 09:08 PM

Your VLOOKUP formula is entered incorrectly.

put this in C2

=VLOOKUP(B2,Sheet1!$B$2:$BV$86,33,0)

normally you could just copy this formula down your column, but you have what appears to be rows in between where you do not use the vlookup. You can just copy and paste the above formula into each cell and then just change B2 to match the cell it needs to look for the lookup value.

**ladygogo** · 2012-10-21, 09:19 PM

holy bejeezus - that formula worked

**ladygogo** · 2012-10-21, 09:51 PM

It worked up until Row 18

=VLOOKUP(C18,Sheet1!B2:AC86,26,0)

Now its not giving me a n/A error, its just showing me the formula in the cell only from then on ARGH.

Can i send you the new attachment?

**ladygogo** · 2012-10-21, 09:56 PM

Its the AA column on the middle tab, Now its just showing the formula, any idea what i'm doing wrong?

**ladygogo** · 2012-10-21, 10:09 PM

How did you make your datarange become $ in the formula?

**tommyt61** · 2012-10-21, 10:18 PM

I went down to AA34 you can pick it up from there.

f4 key will add $ sign to cell range.

**ladygogo** · 2012-10-21, 10:38 PM

Originally Posted by tommyt61

I went down to AA34 you can pick it up from there.

f4 key will add $ sign to cell range.

Do you know how to do that in a Mac? I saw on the tutorial F4 but it didn't work for my mac, do you know another way?

PS, your formula is working brilliantly, I'm almost done, definitely know what went wrong and yeah - would like to know the F4 trick for macs!

you've been a dream!