Find, Match

Hello to all,

I am working on a work related project in materials. Cutting to the chase...

I have two separate worksheets, The first one goes as follows:



P/N Location
1 B
1 A
1 A
1 A
1 A
2 B
2 C
2 D
3 X
3 Y
3 Z

etc....


My second worksheet

2 B
2 C
2 D


What I am trying to do is, Part Number 2 can exist in location B,C,D. By looking at the first spreedsheet, I want excel to find that 2 is located in B. If that holds true then on my second spreadsheet it will spit out a 1 in the next column over (C).

I attempted to make a if statement, it went as follows =if(and(!sheet2A1=!sheet1A1:A9,!sheet2B1=!sheet1B1:B9),"1",na())



I also attempted a vlookup but my spreadsheet is dynamic therefore I cannot have it be solely depended on specific cells, I need a find function to it.

Thank you in advance.

Thank you for replying so quickly.

I kept getting an error with this formula....

In addition, for the vlookup shouldnt it consist of the entire data array on spreadsheet 1?

Disregard my previous reply...

FDibbins,

With the formula that you presented me the argument is always going to be true.

What would be the method of:

1) Find my combination of i.e. 2B (A1&B1) in Sheet 1 from Sheet 2
2) say that that combination exsists by giving me a 1 in sheet 2.

Thank you in advance.