| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning multiple corresponding values using lookup in a list
I am using the following formula to lookup information that is in Column A in
order to return the value in Column B. There is duplicate information in Column A, but different corresponding information in Column B. The following formula works, but when I try to add a row at the top (above the list of information), the formula no longer works. Can you help? =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A $1:$A$40)),ROW(1:1)),2)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning multiple corresponding values using lookup in a list
If, for example, you enter the formula in B50 and copy it down the
column, try... =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A $1:$A$40)-ROW($A$1)+1), ROWS($B$50:B50)),2) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Wk" wrote: I am using the following formula to lookup information that is in Column A in order to return the value in Column B. There is duplicate information in Column A, but different corresponding information in Column B. The following formula works, but when I try to add a row at the top (above the list of information), the formula no longer works. Can you help? =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A $1:$A$40)),ROW(1:1)),2)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning multiple corresponding values using lookup in a list
That worked. Thanks!!!
"Domenic" wrote: If, for example, you enter the formula in B50 and copy it down the column, try... =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A $1:$A$40)-ROW($A$1)+1), ROWS($B$50:B50)),2) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Wk" wrote: I am using the following formula to lookup information that is in Column A in order to return the value in Column B. There is duplicate information in Column A, but different corresponding information in Column B. The following formula works, but when I try to add a row at the top (above the list of information), the formula no longer works. Can you help? =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A $1:$A$40)),ROW(1:1)),2)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple corresponding values using INDEX | Excel Worksheet Functions | |||
| Lookup values in one column to return another | Excel Worksheet Functions | |||
| Extracting/look up data from a list and select multiple instances | Excel Worksheet Functions | |||
| Returning Multiple Values Based on One Value | Excel Worksheet Functions | |||
| Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Linear Mode
