MrExcel Message Board


Go Back   MrExcel Message Board > Question Forums > Excel Questions

Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only.

Reply
 
Thread Tools Display Modes
Old Mar 30th, 2004, 02:05 PM   #1
jimboy
 
Join Date: Apr 2002
Posts: 2,314
Default Lookup & Match?

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
=

A
B
C
D
1
NameSalesComm
2
Steve S01%
3
Steve S1502%
4
Steve S3503%
5
John T01%
6
John T2502%
7
John T5003%
8
9
10
NameSalesFormula need here to bring back these results
11
Steve S751%
12
John T3502%
13
14
Steve S4503%
15
John T1502%
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!
jimboy is offline   Reply With Quote
Old Mar 30th, 2004, 02:45 PM   #2
fairwinds
MrExcel MVP
 
fairwinds's Avatar
 
Join Date: May 2003
Posts: 8,445
Default Re: Lookup & Match?

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?
__________________
"Fair Winds and Following Seas"
fairwinds is offline   Reply With Quote
Old Mar 30th, 2004, 03:16 PM   #3
jimboy
 
Join Date: Apr 2002
Posts: 2,314
Default Re: Lookup & Match?

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!
jimboy is offline   Reply With Quote
Old Mar 30th, 2004, 03:28 PM   #4
DRJ
MrExcel MVP
 
DRJ's Avatar
 
Join Date: Feb 2002
Location: California
Posts: 3,857
Default

Try this
Code:
=SUMPRODUCT(($A$2:$A$7=A11)*($B$2:$B$7
__________________
Excel VBA Training and Certification (Lesson 1 is free)
<hr>

<hr>-Jacob
DRJ is offline   Reply With Quote
Old Mar 30th, 2004, 03:29 PM   #5
Aladin Akyurek
MrExcel MVP
 
Aladin Akyurek's Avatar
 
Join Date: Feb 2002
Location: The Hague
Posts: 39,332
Default Re: Lookup & Match?

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?
Aladin Akyurek is offline   Reply With Quote
Old Mar 30th, 2004, 03:40 PM   #6
jimboy
 
Join Date: Apr 2002
Posts: 2,314
Default Re: Lookup & Match?

Quote:
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 is offline   Reply With Quote
Old Mar 30th, 2004, 04:49 PM   #7
jimboy
 
Join Date: Apr 2002
Posts: 2,314
Default Re: Lookup & Match?

Bump
jimboy is offline   Reply With Quote
Old Mar 30th, 2004, 09:06 PM   #8
fairwinds
MrExcel MVP
 
fairwinds's Avatar
 
Join Date: May 2003
Posts: 8,445
Default Re: Lookup & Match?

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.
__________________
"Fair Winds and Following Seas"
fairwinds is offline   Reply With Quote
Old Mar 31st, 2004, 09:13 AM   #9
jimboy
 
Join Date: Apr 2002
Posts: 2,314
Default Re: Lookup & Match?

Quote:
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%

jimboy is offline   Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT +1. The time now is 06:09 AM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
All contents Copyright 1998-2009 by MrExcel Consulting.