CODEWITHSUNDEEP
excelexcel-formulavlookup
Hacker Dewdie
Hacker Dewdie

Reputation: 321

Excel: Check if a vendor sells a specified product

I have two sheets in Excel

The Table in Sheet 1 contains the list of vendors and the materials they each sell. Each vendor may sell one or more materials, and each material may be sold by one or more vendors. Each line represents one of these relationships:

enter image description here

Information entered into Sheet 2 needs to be checked to make sure it is valid:

enter image description here

For each line on Sheet 2, I want to check that the material entered into Column A is sold by the vendor in Column B, and place the result in Column D.

Example results:

  1. Row 1
    • Material: 1150051
    • Vendor: 209401
    • False
  2. Row 12
    • Material: 1050010201
    • Vendor: 229250
    • True
  3. Row 13
    • Material: 1010210203
    • Vendor: 225780
    • False

Upvotes: 1

Views: 105

Answers (1)

Carrosive
Carrosive

Reputation: 899

It's difficult to understand your problem, but this may be the answer you're looking for. My answer presumes that you simply want a 'True' or 'False' answer if the vendor does sell the material or not for each row in Sheet 2, but it wont give specific references of where in Sheet 1 the information is found.

As there could be a combination of Vendor ID's and Material ID's I would first create a unique field which joins these together. In Sheet 1 use Column C to join them together with:

=A2&B2

In Sheet 2 Column D use the following formula:

=IF(ISERROR(MATCH(B2&A2,Sheet1!C:C,0)),"False","True")

If the combination of Material ID & Vendor ID can be found in Sheet 1 (Column C) then Column D will equal "True"; If it cant be found then Column D will equal "False".

Upvotes: 0

Related Questions