vlookup, with more than one result?

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

Hello,

My problem;
I have a vlookup set up between 2 worksheets with the
possibility of 6 results. I need to record all the
results is there a way insert rows to include all
possible results.

I have tried to use vlookup but may be going down the
totally wrong road, if you have any suggestions or a
macro that will assist me it would be greatly appreciated.

Thanks

JJ
 
I am trying set up a spreadsheet to calculate bonuses,
depending on your position there are certain objectives.
In 1st sheet is a list of employees and their positions
and on the second is the position with their objectives.
I want to extract the objectives for each employee
acording to their position.

SHEET 1
Colum A Colum B
John Smith Sales Manager
Greg Hobbs Branch Manager

SHEET 2
Colum A Colum B
Sales Manager Revenue
Income
America's & Pacific RAC

Branch Manager Utilisation
Branch Revenue
Pre Tax Income

Hope this helps, Thanks!
 
Assuming the list on sheet 2 has a position name in each row (you can
hide the duplicates with conditional formatting), and all objectives for
each position are listed together --

On sheet 1, in cell C2, enter the following formula:


=IF(COUNTIF(Sheet2!$A$2:$A$26,Sheet1!$B2)<COLUMN()-2,"",OFFSET(Sheet2!$B$1,MATCH($B2,Sheet2!$A$2:$A$26,0)+COLUMN()-3,0))

Copy the formula across as far as required, to accommodate the maximum
number of objectives.
Copy the formula down to the last row of employee data.
 
Back
Top