Functions - Looking up a value returning multiple rows

  • Thread starter Thread starter Annie30
  • Start date Start date
A

Annie30

I created a database in Excel, in my first sheet i stored my trainin
information.

I am trying to build in an other sheet a report that will get for eac
company the training they got with all the the columns in my firs
sheet. Some companies received more that one training, i need to pu
all the training they got in my second sheet. Lookup in Excell onl
returns one cell... I am trying to do my search with the company nam
and trying to get all the records under that name:

SHEET 1: DATABASE
A1 : Client Company Name
B1: Begin Training Date
C1 : End Training Date
D1: Training Course Number
Etc...

SHEET 2: REPORT
A1: Where i put the company name i'm looking for

B5:B100 Where i want to have the Begin Training Date
C5:C100 Where i want to have the End Training Date
D5:D100 Where i want to have the Training Course Number
...

I've been trying to do this since 1 month now... can someone HELP
 
Hi
if your data on sheet 1 starts in row try, enter the following array
formula in B5 (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet 1'!$B$2:$D$999,SMALL(IF('sheet
1'!$A$2:$A$999=$A$1,ROW($A$2:$A$999),10000),ROW()-4)-1,COLUMN()-1)
copy this down and to the left
This will return the #REF error then the last matching entry is found.
You may put this in the following error checking formula:
=IF(ISERROR(INDEX(.....)),"",INDEX(....))
also entered as array formula

HTH
Frank
 
Back
Top