+ Reply to Thread
Results 1 to 2 of 2

Vlookup/If? Formula problem?

  1. #1
    Langoose
    Guest

    Vlookup/If? Formula problem?

    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.

  2. #2
    Bob Phillips
    Guest

    Re: Vlookup/If? Formula problem?

    =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)

    "Langoose" <[email protected]> wrote in message
    news:[email protected]...
    > 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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1