Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
I know there has to be a way to do this.
On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
Hi!
As long as the number of values to return for each lookup_value are the same, 3: =INDEX(Sheet2!B$1:B$20,MATCH(A$1,Sheet2!A$1:A$20,0 )+ROWS($1:1)-1,0) Copy down to B3. Biff "tjb" wrote in message ... I know there has to be a way to do this. On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
what if the number of values are different?
"Biff" wrote: Hi! As long as the number of values to return for each lookup_value are the same, 3: =INDEX(Sheet2!B$1:B$20,MATCH(A$1,Sheet2!A$1:A$20,0 )+ROWS($1:1)-1,0) Copy down to B3. Biff "tjb" wrote in message ... I know there has to be a way to do this. On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
Hi,
Try the following formula in B1 of Sheet 1. It assumes that your data start in Row 1 in Sheet 2, and the user enters the lookup text (e.g., ABC) in A1 of Sheet 1. Also, type a bogus text (e.g., "END OF DATA") in the cell at Column A one row below the last row of data in Sheet 2. Modify the range in the formula (now showing as A1:A101)to suit to your data in Column B of Sheet 2 (include the extra row containing "END OF DATA" in the range) =IF(COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A"&(MATCH ($A$1,Sheet2!$A$1:$A$101,0)+ROW(A1)-1)))=COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A$"&(MAT CH($A$1,Sheet2!$A$1:$A$101,0)))),OFFSET(Sheet2!$B1 ,MATCH($A$1,Sheet2!$A$1:$A$101,0)-1,0),"") Auto-fill the formula down column B to a generous number of rows to accommodate the maximum possible column B numbers that a lookup text can have. Regards, B. R. Ramachandran "tjb" wrote: I know there has to be a way to do this. On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
what if the number of values are different?
I had a feeling that would be the case! Then it gets real complicated! See B. R. Ramachandran's reply. Biff "tjb" wrote in message ... what if the number of values are different? "Biff" wrote: Hi! As long as the number of values to return for each lookup_value are the same, 3: =INDEX(Sheet2!B$1:B$20,MATCH(A$1,Sheet2!A$1:A$20,0 )+ROWS($1:1)-1,0) Copy down to B3. Biff "tjb" wrote in message ... I know there has to be a way to do this. On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returns list?
Hi!
Your formula works as long as the source table starts in row 1. If the source table starts in any other row then the references are all out of sequence. Here's a modification based on your formula that's more flexible. Assumes the source table is in the range Sheet2A5:B17: Array entered: =IF(COUNTA(INDIRECT("Sheet2!A"&MAX((Sheet2!A$5:A$1 7=A$1)*(ROW(A$5:A$17)))&":A"&MAX((Sheet2!A$5:A$17= A$1)*(ROW(A$5:A$17)))+ROWS($1:1)-1))=1,OFFSET(Sheet2!A$1,MATCH(A$1,Sheet2!A:A,0)-1+ROWS($1:1)-1,1),"") Biff "B. R.Ramachandran" wrote in message ... Hi, Try the following formula in B1 of Sheet 1. It assumes that your data start in Row 1 in Sheet 2, and the user enters the lookup text (e.g., ABC) in A1 of Sheet 1. Also, type a bogus text (e.g., "END OF DATA") in the cell at Column A one row below the last row of data in Sheet 2. Modify the range in the formula (now showing as A1:A101)to suit to your data in Column B of Sheet 2 (include the extra row containing "END OF DATA" in the range) =IF(COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A"&(MATCH ($A$1,Sheet2!$A$1:$A$101,0)+ROW(A1)-1)))=COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A$"&(MAT CH($A$1,Sheet2!$A$1:$A$101,0)))),OFFSET(Sheet2!$B1 ,MATCH($A$1,Sheet2!$A$1:$A$101,0)-1,0),"") Auto-fill the formula down column B to a generous number of rows to accommodate the maximum possible column B numbers that a lookup text can have. Regards, B. R. Ramachandran "tjb" wrote: I know there has to be a way to do this. On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have something like a VLOOKUP to look at Sheet2 to fill in the data. Cell B1:B3 should then fill in with a range of data from a separate list on Sheet2 Let's say on Sheet2 there is a list of data that looks something like this: ABC 123 456 789 DEF 012 345 678 I hope this makes at least a little sense to at least one of you out there... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
vlookup returns column to the left | Excel Worksheet Functions | |||
VLOOKUP Returns #REF | Excel Worksheet Functions | |||
VLookup returns #VALUE! | Excel Worksheet Functions | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions |