Wherever I've got $100 in my formula (3 times) you will need to make
this $80000 if that's how many records you have. Make the change while
it is still in C2, and then copy it across and down. You may need to
adjust the column widths to see all your returned data.

Why? Will this make a difference????

If you don't make the change then the formula will only look at the
first 100 rows of data in Sheet2.

Hope this helps.

Pete

On Nov 13, 8:10 pm, Lauri wrote:
In actuality, sheet one will have upwards of 600 records with unique
customer info and customer id numbers, sheet two currently has 80,000
records/rows - not all of which have a match (single or otherwise) in sheet
one.

Why? Will this make a difference????



"Pete_UK" wrote:
In Sheet 2 put this formula in C1:

=A1&"_"&COUNTIF(A$1:A1,A1)

and copy down. In Sheet 1 you need a header row, so insert a row at
the top if you don't already have one. You can put "ID" and "Name" in
A1:B1, then in C1 across enter the numbers 1, 2, 3, 4 ... etc.

Then in C2 you can enter this formula:

=IF(ISNA(MATCH($A2&"_"&C$1,Sheet2!$C$1:$C$100,0)), "",INDEX(Sheet2!$B
$1:$B$100,MATCH($A2&"_"&C$1,Sheet2!$C$1:$C$100,0)) )

I have assumed you have 100 rows of data in Sheet2 - adjust to suit,
then copy the formula across for as many columns as you wish. Then
copy these formulae down into as many rows as you need.

Hope this helps.

Pete

On Nov 13, 5:55 pm, Lauri wrote:
Creating a report - What function can I use to look up a value from one
column in sheet 1 and find all rows in sheet 2 that contain the matching
value?

Sheet one contains Customer Identification numbers. (Oh, and the same
Customer can have multi ID numbers - unique to address)
Sheet two contains product information for the customers.

EX:
Sheet 1:
123 Invotech
146 Bank of Trusted Presidents

Sheet 2:
123 spreadsheet
123 word processing
123 accounting
123 payroll
146 accounting
146 pos retail
146 health services

Report should show the customer name and columns across with the product
names.- Hide quoted text -

- Show quoted text -