Another Lookup Situation

[BenjieLop]

Assume I have the following data that tabulates the sale of widgets b
each salesperson for the firt half of the year (6 months):

******* JAN FEB MAR APR MAY JUNE
Richards 10 15 20 25 30 35
Jones 2 4 6 8 10 12
Smith 1 3 5 7 9 11
Watkins 3 6 9 12 15 18
Blake 5 10 15 20 25 30


If I need to know how many widgets, say, Smith sold in the month o
May, I will get an answer of 9 (This procedure I can formulate i
Excel).

Here is where I am stuck at. My first situation is:

1. I want to know who sold, say, 9 widgets in May


and my second situation is:

2. I want to know, say, what month Smith sold 9 widgets.

I hope someone out there can help me with the formulas.

Thanks in advance.


BenjieLo

 

[Domenic]

Hi,

Using your sample table, try:

First Scenario
----------------

=INDEX($A$2:$A$6,MATCH($B$10,OFFSET($A$1,1,MATCH($B$9,$B$1:$G$1,0),5,1),0))

where B9 contains the month and B10 contains the number of widgets

Second Scenario
--------------------

=INDEX($B$1:$G$1,MATCH($B$17,OFFSET($A$1,MATCH($B$16,$A$2:$A$6,0),1,1,6),0))

where B16 contains the Salesperson and B17 contains the number o
widgets.

If you extend your table to include the full year, then you'll have t
change the formula as follows:

=INDEX($B$1:$M$1,MATCH($B$17,OFFSET($A$1,MATCH($B$16,$A$2:$A$6,0),1,1,12),0))

Hope this helps!

 

[BenjieLop]

Hi Domenic,

Thank you ..

 

[Peo Sjoblom]

Assume the table is named Tbl then to get the person who sold 9 in May

=INDEX(Tbl,MATCH(9,INDEX(Tbl,,MATCH("May",INDEX(Tbl,1,),0)),0),1)

use the same logic to find the month but turn it around

=INDEX(Tbl,1,MATCH(3,INDEX(Tbl,MATCH("Smith",INDEX(Tbl,,1),0),),0))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Domenic]

*Hi Domenic,

Thank you ... *

You're welcome!

 

[Alan Beban]

Assume the table is named Tbl then to get the person who sold 9 in May

=INDEX(Tbl,MATCH(9,INDEX(Tbl,,MATCH("May",INDEX(Tbl,1,),0)),0),1)

use the same logic to find the month but turn it around

=INDEX(Tbl,1,MATCH(3,INDEX(Tbl,MATCH("Smith",INDEX(Tbl,,1),0),),0))

The second formula should contain 9 instead of 3.

And on reflection, it's probably more efficient to highlight A1:G6 and
click on Insert|Name|Create and check Top Row and Left Column, click OK
and then use

=INDEX(A2:A6,MATCH(9,May,0),1) to find the person who sold 9 in May,

and

=INDEX(B1:G1,1,MATCH(9,Smith,0)) to find the month in which Smith sold 9.

And with the ranges named this way, the number sold by Smith in May is
simply =Smith May

Alan Beban

 

[Peo Sjoblom]

The second formula should contain 9 instead of 3.

Correct, I tested that it worked with different numbers and forgot to change
it back

And on reflection, it's probably more efficient to highlight A1:G6 and
click on Insert|Name|Create and check Top Row and Left Column, click OK
and then use

=INDEX(A2:A6,MATCH(9,May,0),1) to find the person who sold 9 in May,

and

=INDEX(B1:G1,1,MATCH(9,Smith,0)) to find the month in which Smith sold 9.

And with the ranges named this way, the number sold by Smith in May is
simply =Smith May

True as well, it is definitely more complicated to use a formula that would
work for
different dimensions, I would personally never use that


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Alan Beban]

Assume I have the following data that tabulates the sale of widgets by
each salesperson for the firt half of the year (6 months):

******* JAN FEB MAR APR MAY JUNE
Richards 10 15 20 25 30 35
Jones 2 4 6 8 10 12
Smith 1 3 5 7 9 11
Watkins 3 6 9 12 15 18
Blake 5 10 15 20 25 30


If I need to know how many widgets, say, Smith sold in the month of
May, I will get an answer of 9 (This procedure I can formulate in
Excel).

Here is where I am stuck at. My first situation is:

1. I want to know who sold, say, 9 widgets in May

With B2:B6 named "Jan", C2:C6 named "Feb", etc.

=INDEX(A2:A6,MATCH(9,May,0),1)

and my second situation is:

2. I want to know, say, what month Smith sold 9 widgets.

=INDEX(A1:G1,MATCH(9,INDEX(A2:G6,MATCH("Smith",A2:A6,0),0),0))

Alan Beban

 

[Domenic]

Hi BenjieLop,

I've changed the formulas so they would accomplish two things:

1) Include the full year
2) Allow you to add as many salespeople as you need without having t
constantly change the range for the formulas

Assuming that your table is in Sheet 1, enter the following formulas i
Sheet 2:

Scenario 1
-------------

=INDEX(Sheet1!A:A,MATCH($B$2,OFFSET(Sheet1!$A$1,1,MATCH($B$1,Sheet1!$B$1:$M$1,0),65535,1),0)+1)

where B1 contains the month and B2 contains the number of widgets

Scenario 2
------------

=INDEX(Sheet1!$B$1:$M$1,MATCH(B9,OFFSET(Sheet1!$A$1,MATCH(Sheet2!B8,Sheet1!A:A,0)-1,1,1,12),0))

where B8 contains the salesperson and B9 contains the number o
widgets

Note that the formulas will only find the first occurance. So, fo
example, if two salespeople sold 9 widgets in the month of May, then i
will only return the first salesperson it finds.

For this reason, I wonder if AutoFilter might be a better alternative.

 

[BenjieLop]

Thank you all for the tips. I just came from an out of town busines
trip and it is just only now that I was able to check the Excel board.

I have tested some of the formulas before I left and they do work.
will try all the other (revised) formulas as well.

Again, thanks a whole lot