If formula and Arrays

[mahhogany]

Hi

I have an array of Supplier numbers A1:A20 in a separate sheet 'Tier Suppliers'

I also have another list of Suppliers which amount to 20000 rows. (Col A). I would like to write a nested If AND formula that checks to see if the data in the 20000 rows matches the array and another column (B). If True then to write 'Tier' if False then to take the value in Col B.

Here is my the formula

=IF((AND(A2 ='TierSuppliers'!A1:20,B2="Standard PO")),"Tier", B2)

But it doesn't work. Is this the correct formula to use? I tried SumProduct but that didn't help much either. Please help!

 

[Andrew Poulsom]

Try:

=IF((AND(COUNTIF(TierSuppliers!A1:A20,A2),B2="Standard PO")),"Tier", B2)

 

[exAHSacto]

What about using VLOOKUP?

 

[vds1]

Use Vlookup or Index Formula. Please post some example so that we could provide you accurate formula.

 

[mahhogany]

Try:

=IF((AND(COUNTIF(TierSuppliers!A1:A20,A2),B2="Standard PO")),"Tier", B2)

Amazing and it worked too! I didn't think I could use CountIfs in that manner. Many thanks!

 

[mahhogany]

Use Vlookup or Index Formula. Please post some example so that we could provide you accurate formula.

I don't think Vlookup would have worked in this instance seen as I want to define the value it returns whereas with Vlookup I would have to return a value from the array. I believe that was the same issue I had with index and match.

Thanks anyway.