VLOOKUP Formula coming back with NA#

Hi,

I have created a template for my CRM, and want to creat a snapshot of each entry entered into one report. By doing this I have the user enter in the estimate number (quote reference number) and than all the details relating to it will be summarised in a report.

The formula I have used is
=IF(ISBLANK($D$6)," ",VLOOKUP($D$6,QuoteData,2,FALSE))

Isblank cell (D6) is the entry where you type in the quote number, this refers back to the main data sheet.

It is a simple request however, coming back as NA#, very frustrating.

I have attached an example for your viewing
The workbook is called Ziltek Opportunities register and the two worksheets in relationship to each other with the formula is - Quote Register and Opportunity Reprt

Please help
Thank you

Hi Monza2,

In the vlookup, you are using "QuoteData" as the table array. For vlookup to work, the lookup value (D6) should be in the first column of the table array (Quotedata). However, the first column of the quotedata has date entry. The enquiry number is in the third column. That is why it is returning NA#. The table array should start from E column of Quote register sheet.

Hope you got it.

OMG I should have realised that , thank you so much for your help, worked a treat!!!!
Greatly appreciated

Great. Please make this post as solved

Also just another dilema, I have an array formula
=SUM(IF(MONTH('Quote Register'!$K$13:$K$257)=MONTH(Q$4),IF(YEAR('Quote Register'!$K$13:$K$257)=YEAR(Q$4),IF('Quote Register'!$L$13:$L$257=$B5,IF('Quote Register'!$E$13:$E$257=$D$2,'Quote Register'!$O$13:$O$257,IF($D$2="(ALL)",'Quote Register'!$O$13:$O$257,0)),0),0),0))

however when I add a new line in my sheet the formula moves from $K$13 to $K$14, how do I have it stay always at $K$13 when a new line is entered?

Thank you

How about using indirect("K13") in place of $K$13. Since K13 is entered as text, it wont change.

Hi, How do I insert indirect into my formula, I tried and it came back with error
=SUM(IF(MONTH('Quote Register'!$K$13:$K$257)=MONTH(Q$4),IF(YEAR('Quote Register'!$K$13:$K$257)=YEAR(Q$4),IF('Quote Register'!$L$13:$L$257=$B5,IF('Quote Register'!$E$13:$E$257=$D$2,'Quote Register'!$O$13:$O$257,IF($D$2="(ALL)",'Quote Register'!$O$13:$O$257,0)),0),0),0))

Try this one.

Formula:

Please Login or Register  to view this content.

Hi
I tried this formula back came back with #REF!

Oh it's an array formula! ok try this one.

Formula:

Please Login or Register  to view this content.