Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple corresponding values using INDEX
Hello,
Please help me on getting the correct way to lookup a value and return multiple corresponding values. In sheet1 first row, I list out the dates as: 11 01 05, 11 02 05, 11 03 05 etc... In sheet2 Column A, it is a list of account number; where as Column B is a list of dates. I would like to use the dates as appear in sheet1 and return the corresponding account number. Since there are multiple account number which match with the dates, so, vlookup cannot be used. I tried the following but it doesn't work: =INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1, ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1) Your reply is much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple corresponding values using INDEX
Hi!
=INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1, ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1) Since you're only interested in returning data from a single column it's not necessary to index more than that single column: =INDEX(Sheet2!$A:$B The problem you have is this: ROW(Sheet2!$B$2:$B$6) That has to be the EXACT same size as the range that you have indexed: INDEX(Sheet2!$A:$B Note that ROW(Sheet2!A:B) is not a valid range and would error. Use the actual range and not just the whole column (unless you really are using the ENTIRE column). Try this as a guide: =INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!B$2:B$10=B$ 1,ROW(B$2:B$10)-ROW(B$2)+1),ROWS($1:1))) I'm assuming you know that's an array formula. Biff "BubbleGum" wrote in message ... Hello, Please help me on getting the correct way to lookup a value and return multiple corresponding values. In sheet1 first row, I list out the dates as: 11 01 05, 11 02 05, 11 03 05 etc... In sheet2 Column A, it is a list of account number; where as Column B is a list of dates. I would like to use the dates as appear in sheet1 and return the corresponding account number. Since there are multiple account number which match with the dates, so, vlookup cannot be used. I tried the following but it doesn't work: =INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1, ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1) Your reply is much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple corresponding values using INDEX
Thank you for your reply.
I'm just wondering is there a limit on setting how large the array is ? The reason I'm asking is because when I include an array into the formula, from $A$1:$B:3500, it only works for the first column but not another column using similar formula but return different values. The second column only shows "#N/A" unless I reduce the range from 1 to 500 only. Since I do need to include that range of cells, please help on solving the problem. Thank you very much. -Bubblegum "Biff" wrote: Hi! =INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1, ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1) Since you're only interested in returning data from a single column it's not necessary to index more than that single column: =INDEX(Sheet2!$A:$B The problem you have is this: ROW(Sheet2!$B$2:$B$6) That has to be the EXACT same size as the range that you have indexed: INDEX(Sheet2!$A:$B Note that ROW(Sheet2!A:B) is not a valid range and would error. Use the actual range and not just the whole column (unless you really are using the ENTIRE column). Try this as a guide: =INDEX(Sheet2!A$2:A$10,SMALL(IF(Sheet2!B$2:B$10=B$ 1,ROW(B$2:B$10)-ROW(B$2)+1),ROWS($1:1))) I'm assuming you know that's an array formula. Biff "BubbleGum" wrote in message ... Hello, Please help me on getting the correct way to lookup a value and return multiple corresponding values. In sheet1 first row, I list out the dates as: 11 01 05, 11 02 05, 11 03 05 etc... In sheet2 Column A, it is a list of account number; where as Column B is a list of dates. I would like to use the dates as appear in sheet1 and return the corresponding account number. Since there are multiple account number which match with the dates, so, vlookup cannot be used. I tried the following but it doesn't work: =INDEX(Sheet2!$A:$B, SMALL(IF(Sheet2!$B$2:$B$6=Sheet1!$B$1, ROW(Sheet2!$B$2:$B$6)), ROW(1:1)),1) Your reply is much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Return Consecutive Values | Excel Worksheet Functions | |||
Multiple X-Axis Values | Charts and Charting in Excel | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |