Hi
I’m hoping that someone can help please.
I’ve read, and re-read, articles on error messages regarding vlookup but none of the answers seem to work.
I’m trying to produce a simple invoice. I use a data validation on C15-C19 to force only valid data being selected. I now want to use vlookup to read those cells and, using the array M7:O23, enter the price into the invoice at the corresponding cell. However, I keep getting #N/A error. I won’t bore you that I’ve also been trying it by having the lookup data on a seperate worksheet !
Any ideas anyone please.
Thanks in advance
Bob
VLOOKUP(C15,$M$7:$O$23,3,0)
I suggest you to read an article about correct usage of VLOOKUP, that exists in our help:
This function checks if a specific value is contained in the first column of an array.
You are looking for “bottle opener - dog” in column M!
change $M$7 to $N$7 … and probably ;3; to ;2;
Hi
Although it is not clear, in the image, on one line it is with column M and 3 and on the second line it is column N and 2. So, yes, I DID read the help pages and enterred it as shown but, on both, I get the same result.
Could it be because I am auo-enterring via a validity selector?
Bob
Provide a sample ODS.
woodcraft-sales.ods (37.3 KB)
Hi
I’ve tried again, taking out the data from the main sales sheet and refering to a seperate sheet using
Blockquote =VLOOKUP(B4,$jobsheet.$B$2:$D$18,3,0) Blockquote <
and it now works. I still get #N/A when it’s referring to an empty cell, which is annoying but something I can live with (we’re raising money for charity and they won’t care what the spreadsheet looks like 
)
Regards
Bob
=IFNA(VLOOKUP(B5;$jobsheet.$B$2:$D$18;3;0);"")
Or return 0 on NA; you then may apply a format to the cells that does not show 0; or you may disable showing zeroes universally; or you may filter errors also in column with sums … possibilities are countless 