To help clarify what is happening, look at the middle of the formula:


IF( Data!$A$1:$A$5000 = A1 , Data!$B$1:$B$5000 , "" )


If the value in column A (Category) from Data matches the Category entered in
cell A1, it returns the value from column B (Sales Range) in Data, otherwise it
returns "". Because the formula references multiple cells and was array
entered, the result is an array of values and now your formula looks something
like this:


=LOOKUP(B1,
{"", "", "", 0, 15000, 35000, 65000, 10000, "", "", ""},
Data!$C$1:$C$5000)


The LOOKUP compares the sales value entered in B1 to the array and returns the
corresponding value in column C (Rep Calls) from Data, following the rules of
the LOOKUP function.


Mattlynn via OfficeKB.com wrote:
Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

I see its looking up the category B1 in a tab called data in col A, and the
sales ranges in Col B, and bringing back the rep calls in Col C, but how does
it know what mysales actual was

Thanks
Matt



Glenn wrote:
I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail.
[quoted text clipped - 34 lines]
over the moon.
Many Thanks
Array formula (commit with CTRL+SHIFT+ENTER):

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000)