Lookup & Match?

[jimboy]

Hi - I need a formula to look up a name and then find the nearest figure under the sales figure in the table - and bring back the data in the third column of the table.

i.e. in A1:C7 is the table, in A11 & B11 is the values to look up and C11 is the were I want the formula.

Thanks.
PS I know I'm crap at explaining this!

******** ******************** ************************************************************************>

Microsoft Excel - Book8 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D
1	Name	Sales	Comm
2	Steve S	0	1%
3	Steve S	150	2%
4	Steve S	350	3%
5	John T	0	1%
6	John T	250	2%
7	John T	500	3%
8
9
10	Name	Sales	Formula need here to bring back these results
11	Steve S	75	1%
12	John T	350	2%
13
14	Steve S	450	3%
15	John T	150	2%
Sheet1

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


PPS. Have done it naming ranges and using vlookup & indirect - but it takes ages to name all the ranges!

[fairwinds]

Hi,


=INDEX($C$2:$C$7,MATCH(MIN(IF($A$2:$A$7=A11,ABS($B$2:$B$7-B11),"")),IF($A$2:$A$7=A11,ABS($B$2:$B$7-B11),""),0))

Array entered in C11.

But 75 is equally close to 0 as to 150 is it not?

[jimboy]

Sorry, it must have been the way I explained it (I said I was crap!)

If Steve S has a sale less than 150 then it should bring back 1%
If Steve S has a sale more than or equal to 150 and less than 350 then it should bring back 2%
If Steve S has a sale more than or equal to 350 then it should bring back 3%

Same with John T but with different values.

If I enter 76 in B11 it brings back 2% (should be 1%)

I would have amended your formula if I could work it out!

[DRJ]

Try this

=SUMPRODUCT(($A$2:$A$7=A11)*($B$2:$B$7

[Aladin Akyurek]

The second and third column of your lookup table are in ascending order. Is this accidental or deliberate? If the latter, why are the names in ascending order?

[jimboy]

Originally Posted by Aladin Akyurek

The second and third column of your lookup table are in ascending order. Is this accidental or deliberate? If the latter, why are the names in ascending order?

Accidental - they could be in any order.

DRJ - Your formula works on the example but the Commissions in column C are only an example, they could be anything, I need the formula to look at the table - not do a calculation.

Thanks.

[jimboy]

Bump

[fairwinds]

If i understand you correctly, the formula could be simpler.

=INDEX($C$2:$C$7,MATCH(B11,IF($A$2:$A$7=A11,$B$2:$B$7,"")))

Still array entered in C11

Sales must be sorted ascending (within each name)

... and I don't agree with the last result for John T in the example you posted. Should be 1% I think.

[jimboy]

Originally Posted by fairwinds

... and I don't agree with the last result for John T in the example you posted. Should be 1% I think.

The formula great, thanks.

Your right, it should have been 1%