Hi A'80,
Does this fit your request?
Details is the 'catalogue' containing a list of items carried and information about them.
Main is the order sheet on which the Item Number is entered in column A.
Information in columns B through D is retrieved from the Details table using a Lookup function.
For the example, there is a single formula, entered into Main::B2, then filled right to D2 and down to the last row on Main.
All data on the Details table is entered directly. There are no formulas on this table.
Main::B2: =IFERROR(VLOOKUP($A,Details :: $A:$D,COLUMN(),FALSE),"")
Fill right to D2. Fill down to last row of Main.
FALSE here tells VLOOKUP to accept only an exact match when searching for the item number. If a match is found, COLUMN() tells VLOOKUP the column of the lookup table ("Details") from which to return the correct information.
Any value in column A of Main that is not found in column A of Details will cause an error. IFERROR traps the error, and places a null string ( "" ) in the cell containing the formula.
The formula shown is for a lookup table on the same Sheet as the Main table. If "Details" is the ONLY table in the Document with that name, the formula will be the same if Details is paced on a different Sheet from Main.
REgards,
Barry