Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
I have 1 table with STOCK CODE and CONTRACT VENDOR
I have 1 table with PURCHASE ORDER, STOCK CODE, VENDOR, AND CONTRACT
I need a formula that uses the stock code from the Purchase order table to search the stock code - contract vendor table and return whether or not a vendor is one of many available contract vendors for that stock code.
Trying to end with a result that looks like this:
You can use:
=IF(COUNTIFS($A$2:$A$10,B13,$B$2:$B$10,C13)>0,"Yes","No")
Countifs will return 1 when there is a match Stock code and Contract Vendor with the purchase Table
When no match (if only Stock code) Countifs will return 0
If will return Yes when True and No when False
B13 is the Stock Code
C13 is the Vendor in the Purchase Table
You can use a formula like this one:
=IF(ISNUMBER(AGGREGATE(15,6,$B$2:$B$10/(($B$2:$B$10=G2)*($A$2:$A$10=F2)),1)),"Yes","No")
