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

ladygogo

New member
Joined
Oct 21, 2012
Messages
12
Reaction score
0
Points
0
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
 

Attachments

  • vlookuphelp.xlsx
    90.2 KB · Views: 65
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.
 
Last edited:
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?
 
Its the AA column on the middle tab, Now its just showing the formula, any idea what i'm doing wrong?
 
How did you make your datarange become $ in the formula?
 
I went down to AA34 you can pick it up from there.

f4 key will add $ sign to cell range.
 

Attachments

  • PROD­ MOD.xlsx
    115.9 KB · Views: 19
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!
 
Add $ to cell range on Mac

Apple key + T

at least that is what i was told.
 
Last edited:


Oh poo here we go again! I thought I could do this all by myself. Here's my formula for a different cell:
=IF(ISNA(VLOOKUP(C4,Sheet1!$B$2:$BW$86,22,0)),"",VLOOKUP(C4,Sheet1!$B$2:$BW$86,22,0))

Why is it just showing the formula in the cell??? As you know this happened before but you corrected it. I'd like to correct it myself in the worksheet this time. Is it the formula or my formatting? argh...

thanks in advance tommy guru - you're helping me a lot today!

Oh snap - looks like its my formatting. I need to delete the =, go to format, format as general then put the = back and it spits the value out. i'm going bat crazy here. Answered my own question. Thanks Tommy! (and thanks for answering my conditional formatting question in another thread, not hugely happy with the result but it'll do for my purposes).
 
Last edited:
Back
Top