Array Formula

[mikeymay]

I have a list of data that contains an area description within the cells text string
E.g. the text string is as follows 'Project Name - Area Rate - Name'

From the above text string I am using an array formula to return a rate based on the part of the text that is 'Area Rate'

=IF(E282="","",INDEX(D_PayRatesAreas,SUMPRODUCT(ISNUMBER(SEARCH(D_PayRatesAreas,E282))*(ROW(D_PayRatesAreas)-3))))

E282 is the cell containing the text string 'Project Name - Area Rate - Name'
D_PayRatesAreas is a list of areas with the associated pay rates

This has been working until one of the pay rates needs to be in the list twice as a specific manager needs to be associated with this pay rate and there are two managers associated with this one pay rate.

Is it possible to adapt this formula to deal with the 'Area Name' pay rate being in the lookup list twice?

TIA

 

[Oscar Cronquist]

=IF(E282="","",LOOKUP(1,1/SEARCH(PayRatesAreas,E282),PayRates))

PayRatesAreas is the areas column, Payrates is the pay rates column.

 

[mikeymay]

Is this supposed to be entered as an array formula?

I have tried as an array and not and it is returning #NAME ?

 

[Oscar Cronquist]

Is this supposed to be entered as an array formula?

No

I have tried as an array and not and it is returning #NAME ?

You need to change PayRatesAreas and Payrates in the formula to their corresponding cell reference in your workbook.