Returning cell information

G

gregory666

New Member
Joined
Sep 9, 2005
Messages
16
Hi:

I have a spreadsheet which runs ( simplified) something like this:

Quantity ADR1 ADR3 CITY STATE ZIP PHONE#
50 Address Contact City State zip number
What I am trying to do is is return ADR1, based on phone number , but only for the listing where the Quanity is the highest. How should I proceed?Needless to say, there are multiple listings. I have figured out how to sum quantities based on common phone numbers.

Thanks

Greg
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Returning Cell Information

Thanks Eric for trying to help; for some reason after downloading the HTML, I can not open it.

Anyway, I have simplified my question further. Suppose the following three column list:

Quanity Address Phone
50 xyz 914-8**-0000
45 xyb 914-8**-0000
2 xyo 914-8**-0000

What I am trying to do is associated an address from one huge list with a list of phone numbers. My criteria would be to select the largest quantity (50), associated with a unique phone number 914-8**-0000, and return the address from the cell associated with it.

(Sorry Kristy)

Greg
 
Upvote 0
do you mean:
search phone number in "large" list
you get a "little" list
within that "little" list find the largest value
return the address next to that value
 
Upvote 0
Hello, Gregory,
try this
Code: 
F2 {=MAX(IF(($C$2:$C$10=E2)*$A$2:$A$10,$A$2:$A$10))}
G2 =INDEX($B$2:$B$10,MATCH(F2,$A$2:$A$10,0))
H2 {=INDEX($B$2:$B$10,MATCH(MAX(IF(($C$2:$C$10=E2)*$A$2:$A$10,$A$2:$A$10)),$A$2:$A$10,0))}
array formulas (F2 and H2) to confirm with CONTROL-SHIFT-ENTER
Map1.xls
ABCDEFGH
1QuantityAddressPhonePhonemaxaddressall in one
23b2914-8**-0001914-8**-000050b3b3
350b3914-8**-0000
43b4914-8**-0001
545b5914-8**-0000
62b6914-8**-0000
73b7914-8**-0001
84b8914-8**-0002
95b9914-8**-0003
106b10914-8**-0004
reports

take care whether you want absolute addresses or not if you want to copy down for instance

kind regards,
Erik
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Extract Address information from large text file
Replies
5
Views
425
hayden
H
F
  • Solved
How to format a vendor list when vendors offer several different products?
Replies
8
Views
163
Micron
M
W
  • Solved
Cleaning up contact information
Replies
3
Views
301
Peter_SSs
Peter_SSs
D
  • Question
Inserting an image based on the content of the adjacent cell
Replies
0
Views
484
DarrelKelly
D
R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
356
realtoast
R

Forum statistics

Threads
1,214,978
Messages
6,122,549
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top