| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup of multiple values
I am trying to lookup Ex: "JOE" in column A and return the value
corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
This is my answer to a similar question today. See if you can adapt it
With the text in B1, C1, D1, dates in A2:A3; data in B2:D4 date Aberdeen Celtic Rangers 23/11/2008 12000 52000 49000 24/11/2008 14000 45000 47000 25/11/2008 15000 65000 52000 In G1 enter the team to find: Rangers In H1 locate its position in the list of teams with =MATCH(G1,B1:D1,FALSE) ; this returns 3 since the team is in position 3 In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3 since today's date in the third position Locate the required data with =INDEX(B2:D4,H2,H1) This returns the expected 52000 Combine all this into one formula as =INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B 1:D1,FALSE)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "airsg" wrote in message ... I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
=IF(ISERR(SMALL(IF(rngA="Joe",ROW(rngB)),ROWS($1:1 ))),"",INDEX(rngB,SMALL(IF(rngA="Joe",ROW(rngB)),R OWS($1:1))))
ctrl+shift+enter, not just enter copy down as far as needed "airsg" wrote: I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
The example assumes a unique identifier (date) which I do not have and my
data is layed out like this: A B AMY 5765 BOB 3234234 JOE 34256244 JOE 6687 JOE 86523 JOE 2789894 I am trying to return the values in multiple cells based on the input JOE lets say in H1 of another sheet "sheet1" JOE 34256244 6687 86523 2789894 Steve -- Steve "Bernard Liengme" wrote: This is my answer to a similar question today. See if you can adapt it With the text in B1, C1, D1, dates in A2:A3; data in B2:D4 date Aberdeen Celtic Rangers 23/11/2008 12000 52000 49000 24/11/2008 14000 45000 47000 25/11/2008 15000 65000 52000 In G1 enter the team to find: Rangers In H1 locate its position in the list of teams with =MATCH(G1,B1:D1,FALSE) ; this returns 3 since the team is in position 3 In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3 since today's date in the third position Locate the required data with =INDEX(B2:D4,H2,H1) This returns the expected 52000 Combine all this into one formula as =INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B 1:D1,FALSE)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "airsg" wrote in message ... I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
Is the data sorted or grouped together like you sample shows?
-- Biff Microsoft Excel MVP "airsg" wrote in message ... The example assumes a unique identifier (date) which I do not have and my data is layed out like this: A B AMY 5765 BOB 3234234 JOE 34256244 JOE 6687 JOE 86523 JOE 2789894 I am trying to return the values in multiple cells based on the input JOE lets say in H1 of another sheet "sheet1" JOE 34256244 6687 86523 2789894 Steve -- Steve "Bernard Liengme" wrote: This is my answer to a similar question today. See if you can adapt it With the text in B1, C1, D1, dates in A2:A3; data in B2:D4 date Aberdeen Celtic Rangers 23/11/2008 12000 52000 49000 24/11/2008 14000 45000 47000 25/11/2008 15000 65000 52000 In G1 enter the team to find: Rangers In H1 locate its position in the list of teams with =MATCH(G1,B1:D1,FALSE) ; this returns 3 since the team is in position 3 In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3 since today's date in the third position Locate the required data with =INDEX(B2:D4,H2,H1) This returns the expected 52000 Combine all this into one formula as =INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B 1:D1,FALSE)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "airsg" wrote in message ... I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
Yes, the data is sorted in ascending alphabetical order.
-- Steve "T. Valko" wrote: Is the data sorted or grouped together like you sample shows? -- Biff Microsoft Excel MVP "airsg" wrote in message ... The example assumes a unique identifier (date) which I do not have and my data is layed out like this: A B AMY 5765 BOB 3234234 JOE 34256244 JOE 6687 JOE 86523 JOE 2789894 I am trying to return the values in multiple cells based on the input JOE lets say in H1 of another sheet "sheet1" JOE 34256244 6687 86523 2789894 Steve -- Steve "Bernard Liengme" wrote: This is my answer to a similar question today. See if you can adapt it With the text in B1, C1, D1, dates in A2:A3; data in B2:D4 date Aberdeen Celtic Rangers 23/11/2008 12000 52000 49000 24/11/2008 14000 45000 47000 25/11/2008 15000 65000 52000 In G1 enter the team to find: Rangers In H1 locate its position in the list of teams with =MATCH(G1,B1:D1,FALSE) ; this returns 3 since the team is in position 3 In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3 since today's date in the third position Locate the required data with =INDEX(B2:D4,H2,H1) This returns the expected 52000 Combine all this into one formula as =INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B 1:D1,FALSE)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "airsg" wrote in message ... I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Vlookup of multiple values
Try this:
H1 = Joe Enter this formula in H2 and copy down until you get blanks meaning all the data has been extracted: =IF(ROWS(H$2:H2)<=COUNTIF(A$1:A$6,H$1),INDEX(B$1:B $6,MATCH(H$1,A$1:A$6,0)+ROWS(H$2:H2)-1),"") -- Biff Microsoft Excel MVP "airsg" wrote in message ... Yes, the data is sorted in ascending alphabetical order. -- Steve "T. Valko" wrote: Is the data sorted or grouped together like you sample shows? -- Biff Microsoft Excel MVP "airsg" wrote in message ... The example assumes a unique identifier (date) which I do not have and my data is layed out like this: A B AMY 5765 BOB 3234234 JOE 34256244 JOE 6687 JOE 86523 JOE 2789894 I am trying to return the values in multiple cells based on the input JOE lets say in H1 of another sheet "sheet1" JOE 34256244 6687 86523 2789894 Steve -- Steve "Bernard Liengme" wrote: This is my answer to a similar question today. See if you can adapt it With the text in B1, C1, D1, dates in A2:A3; data in B2:D4 date Aberdeen Celtic Rangers 23/11/2008 12000 52000 49000 24/11/2008 14000 45000 47000 25/11/2008 15000 65000 52000 In G1 enter the team to find: Rangers In H1 locate its position in the list of teams with =MATCH(G1,B1:D1,FALSE) ; this returns 3 since the team is in position 3 In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3 since today's date in the third position Locate the required data with =INDEX(B2:D4,H2,H1) This returns the expected 52000 Combine all this into one formula as =INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B 1:D1,FALSE)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "airsg" wrote in message ... I am trying to lookup Ex: "JOE" in column A and return the value corresponding to "JOE" in column B. The problem is that there are 5 "JOE"s listed and I want to run formulas in five cells to show the 5 corresponding values to "JOE" from column B. Any help with this would be appreciated. -- Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
| vlookup on multiple values | Excel Worksheet Functions | |||
| Vlookup multiple values | Excel Worksheet Functions | |||
| how do i get multiple values in vlookup? | Excel Worksheet Functions | |||
| vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions | |||
Linear Mode
