• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to pick up rate from matching a column & row combination

Peter Bartholomew

Well-Known Member
It might be more complicated than that. At the moment the Rate Table is in a block diagonal form suggesting you add a new set of Work Descriptions for each Variety. If that is the case you may well finish up with duplicated Work Descriptions applying to different Varieties.

The alternative is that you identify common Work Descriptions but that means that, for a given Variety, the Rates will be scattered over the column. If that is the case it would be better to sort the Work Descriptions by weight.

In the case of the first strategy, you would first need to identify the correct column in the Rate Table, defining 'RateList' (say)
= INDEX( RateTable, 0, MATCH(Table1[@VARIETY], Variety, 0 ) )
and only then use the Work Description, but filtering out those entries that do not apply to the chosen Variety
= INDEX( RateList, MATCH( [@[WORK DESCRIPTION]], IF( RateList>0, WorkDescription, "" ), 0 ) )

If, on the other hand, you can guarantee that the Work Descriptions are unique, then @GraH - Guido's suggestion will work. Using my structured references for comparison purposes, that would be
= INDEX( RateTable, MATCH( [@[WORK DESCRIPTION]], WorkDescription, 0 ), MATCH(Table1[@VARIETY], Variety, 0 ) )

Peter Bartholomew

Well-Known Member
With the new dynamic arrays there is an alternative to LOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH, namely, FILTER.
In the attached, I have first filtered the range 'RateTable' horizontally to select the relevant column 'RateFiltered'
= FILTER( RateTable, ( Variety=ContractorAccountFilters[@VARIETY] ) )

I then find the specific rate by filtering the resultant column, selecting the row in which the Work Description matches and the value is not blank.
= FILTER( RateList, ( WorkDescription = [@[WORK DESCRIPTION]] ) * ( RateList>0 ) )
Note that the second formula calls the first for evaluation twice.