Formula

HZAR

Hi

I have a scenario like below

If Award Type is MP, then Vlook up buyer name

If Award Type is non MP and Approval required is tick, then vlookup buyer name

The formula that I am thinking as below, but i think it is wrong. Can help to show me the correct formula

=IF(AND([Approval]@row = "Yes", [Award Type]@row="Non MP")), OR([Award Type]]@row = "MP", VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

Mark.poole

@HZAR

I apologize I had a typo in the formulas. I corrected them below

=IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

Or use

=IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

Itai Perez

Hi,

It seems that the Vlookup is the same either way right? What are the return values? can you send some screenshots please?

Are there any other options that are different than MP/non MP?

Thanks

Mark.poole

@HZAR

check boxes are Boolean. meaning they are 1 or 0. To get your formula to work.

=IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

If it is not a check box then you need the below formula

=IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

HZAR

Hi @Mark.poole

the formula that you have told is giving me Incorrect argument error. Need your help to advise. Thanks

Mark.poole

@HZAR

I apologize I had a typo in the formulas. I corrected them below

=IF(OR(AND([Approval]@row = 1, [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

Or use

=IF(OR(AND([Approval]@row = "Yes", [Award Type]@row="Non MP"), [Award Type]@row = "MP"), VLOOKUP(Commodity@row, {BuyerRange 1}, 3, 0))

HZAR

Hi @Mark.poole Thanks for the help to show the correct way.