View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Lookup duplicate items in a list

Source data in Sheet2 as posted, from row2 down

In Sheet1,
Input in A2 (Cust), eg: R0335

Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
A B C D
1 Customer Destnation Rate Value
2 R0335 =vlookup..
3
4


Sheet2
A B C D
1 Customer Destination
2 A0022 Ireland
3 D1034 Scotland
4 R0335 Ireland
5 A0022 Wales
6 R0335 France
7 C0078 Spain
8 R0335 Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall