how to look up the value in another sheet

[xiaodan86]

I have one input sheet use to add data to another sheet(Database). The
input sheet is like a checklist.
In the input sheet, I have one combox box to select the tool id.
Since each tool will have its own spec for each inspection (upper
limit and lower limit), so I would like to change the spec for the
tool once the tool id is change.
How can I feel up the spec based on the tool selected?
Use VLOOKUP function or use MATCH together with INDEX function?

my input sheet is
tool id(combo box)
inspection upperlimit lower limit results
a
b
c

I already keyed the value for each tool spec in another sheet(spec)
the format will be like this:
tool1 tool
2 tool3 ......
inspection lower limit upperlimit lower limit upperlimit
a 0 0.1
0 0.11
b 1 2
1.2 1.8
c
...

 

[Bernie Deitrick]

Xiaodan,

Make up your limit table like so, in cells A1"GXXXX on the sheet named 'spec'

Tool ALowerLimit AUpperLimit BLowerLimit BUpperLimit CLowerLimit CUpperLimit

then use these six formulas:
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,2,False)
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,3,False)
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,4,False)
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,5,False)
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,6,False)
=VLOOKUP(tool Id from combobox, spec!$A$1:$G$1000,7,False)

to get the six limit values from your table.

HTH,
Bernie
MS Excel MVP