On Mon, 29 Nov 2004 08:31:51 -0500, Debra Dalgleish
contemplated and re-advised:

Sorry, I misread your question. To prevent data being returned when the
code isn't an exact match with an entry in the table, you could use
INDEX/MATCH, instead of VLOOKUP. For example:

=INDEX($B$2:$B$13,MATCH(TRUE,EXACT(D3,$A$2:$A$13), 0))

entered as an array formula (Ctrl+Shift+Enter)

In this example, the codes are in A2:A13, the number to return are in
B2:B13, and the lookup value is in cell D3.

If the table contains 200x16fl, an entry of 200*16fl in cell D3 will
return #N/A, as will 200X16fl (X is not an exact match to x).

Or, instead of letting the user type a value to match, you could use
data validation to provide a dropdown list of values from the lookup table.

Managed to write the whole above Formula, (with my parameters),and
enter it as an Array, but nothing happened...I just get a #REF in the
row/colum I want the Data to appear.
It may be because I have the 'Steel Database 2004-7.xls'! in a
separate Workbook, as it is so Large, and we have always addressed it
this way with a VLOOKUP function in 3 separate OFFSETS.
????
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
As this original post is too old I'm posting again to request the
correct Syntax on the INDEX formula, entered as an array. suggested
above, given that I'm using 2 separate Workbooks.
As I am an Estimator let's call Workbook No. 1 "Estblank 2004-3.xls".
This is where I want the Data to appear that's extracted from Workbook
No. 2, namely "Steel Database 2004-7", which has Product Description
on R,C 2,B, thru to R,C 1200,C and the other values columns offset
similar to below (all from R3:R1200.
So in workbook No, 1, I enter the Product Description in say, RC 5,G,
and expect it to extract EXACT Unit Data Values from Workbook No. 2
The Unit Values of "Mass", "M2" and "$ Cost" should appear in Workbook
No. 1 in RCs 5,M, 5,O and 5,Q respectively, so that Quantities can be
entered and Extensions calculated from the above Unit Values.

Hope this is enough to get the Correct Syntax for the above Formula to
be ultimately entered as an Array.

Additional to that I need an IF Function up front to test empty Cells
in Column G, Workbook No.1 to return "" in all Unit Values.

If anyone would like copies of the Current Workbooks to better
appreciate what I'm doing please indicate and I'll email them as one
zipfile.



-- "if you can see it coming, head it off at the pass, else put the wagons in a circle"
-- Please reply to this ng as:
-- my email adress is 100% faked to prevent proliferation of SPAM!!
-- Regards

-- Clarence Crow