Vlookup/If? Formula problem?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Having a bit of trouble working out a formula and wonder if anyone can help
me...

I'm trying to look up a value (quantity of parts on hand) based on two
criteria (a part number and a revision)... the worksheet I'm looking up is
laid out the same as the one I want to populate with 'part number' in column
A, 'revision' in column B and the 'on hand quantity' in column C... the
problem arises as in column A there may be several of the same part number
with differering revisions in column B so a normal lookup won't work. The
formula needs to look at the ranges and say... if you have this part number
in column A and this revision in column B then return the value in column C.

Presently I'm creating a unique identifier by adding the contents of the two
cells (part number and revision) together and looking up on that but a
formula would be more handy!

Cheers.
 
=INDEX(C2:C200,MATCH(1,(A2:A200="part number")*(B2:B200="revision"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top