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)) |
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)) |
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)) |
All times are GMT +1. The time now is 01:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com