# If >1 criteria is found, give me one flat rate regaurdless of units

#### jan934

##### New Member
=IF(AND(C5=762,E5>0),VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE)*E5,"") is what I have so far. I need the formula to look up my code 762, and if the criteria is met, give me one flat rate (\$94.35) regardless of how many times this code/units have been entered.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I'm guessing that you mean if D5 is 762 then don't multiply the result of the VLOOKUP by E5. If that's the case:

=IF(AND(C5=762,E5>0),VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE)*IF(D5=762,1,E5),"")

=IF(C5=762,VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE),VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE)*E5)

This is what i have now. I need Excel to only pull the value for this code once, regardless of how many times it has been entered in the C column. For example if the code was entered on 5 lines, to pull the reimbursement rate for the top code and \$0.00 for the rest. I hope this makes sense. THanks for your help!
 1

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

I'm guessing that you mean if D5 is 762 then don't multiply the result of the VLOOKUP by E5. If that's the case:

=IF(AND(C5=762,E5>0),VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE)*IF(D5=762,1,E5),"")

GOT IT !
=IF(AND(C5=762,G5>1),0,IF(C5=762,VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE),VLOOKUP(D5,Sheet2!\$A\$1:\$E\$6745,5,FALSE)*E5))

Replies
6
Views
1K
Replies
5
Views
245
Replies
13
Views
676
Replies
0
Views
628
Replies
3
Views
370

1,196,048
Messages
6,013,088
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back