How to create formula that compares 2 columns on 2 different sheets & returns a value

Vera

Hi,

I need to figure out how to pull in the right dollar amount from another sheet into the main one. The $ amount will be based on 2 criteria:

1. The deal type
2. The number of doctors

The deal type is a column on both sheets, same with the number of doctors. So I need to compare those 2 columns on both sheets (helper sheet & main sheet), and have the formula bring in the corresponding $ amount onto the main sheet (SPIFF amount column). Below is the helper sheet. For instance, if the main sheet has a row with "CSA" and 14 doctors, I want the formula to key in $25,000 on my main sheet.

See below my main sheet. The $25,000 would populate in the SPIFF Amount column:

How can I accomplish this?

johngraham78

@Vera, You could create a helper column on each sheet that concatenates these two values ( Deal Type and # o Doctors), then use Index/Match to find/return the correct dollar value.

johngraham78

@Vera, You could create a helper column on each sheet that concatenates these two values ( Deal Type and # o Doctors), then use Index/Match to find/return the correct dollar value.

Vera

This worked like a charm! Thank you so much.