lookup function

  • Thread starter Thread starter Clinton
  • Start date Start date
C

Clinton

I need to lookup and insert values from sheet 1 into sheet 2. names are not
always alphabetical and can change order.
a b
1 name type
2 fred A/L
3 fred PDO
4 fred S/L
5 bob A/L
6 bob PDO
7 bob S/L

there is always 3 results for each name to be copied from previous sheet.
can only get first type returning in place of the others for each name.

Can anyone suggest something.
thanks
 
Are the three results that you need always grouped in consecutive rows?
If so, you could use MATCH to find the first row number, then INDEX
functions to find the appropriate entries.
=INDEX(Sheet1!B:B,MATCH("fred",Sheet1!A:A,0)) would get you the first entry
(just like vlookup), then INDEX(Sheet1!B:B,MATCH("fred",Sheet1!A:A,0)+1)
would get you the second (because the +1 moves down the list one row), etc.
 
Thanks very much. does what i needed


bpeltzer said:
Are the three results that you need always grouped in consecutive rows?
If so, you could use MATCH to find the first row number, then INDEX
functions to find the appropriate entries.
=INDEX(Sheet1!B:B,MATCH("fred",Sheet1!A:A,0)) would get you the first entry
(just like vlookup), then INDEX(Sheet1!B:B,MATCH("fred",Sheet1!A:A,0)+1)
would get you the second (because the +1 moves down the list one row), etc.
Click to expand...
 
Back
Top