Extracting Officer Codes Based on City Selection in Excel

[rasika_99]

I have a Excel sheet (Excel 2007) containing the names of cities and their corresponding officer codes. I need to extract the relevant officer codes when selecting a city from a dropdown menu in another sheet. Could anyone assist me with this? A screenshot is attached.

 

[sxhall]

Hi rasika_99,

This will do what you need, just add this formula in to cell D6 only. Formula will list all the matches from cell D5 downwards.

Also the formula will remove any duplicates.

PS change the 'Sheet5' to the name of your sheet and the refereces accordingly.

=UNIQUE(FILTER(Sheet5!$B:$B,Sheet5!$A:$A=$D$3))

My examples...

Also if you conditional formatting to column D, to add border if cell is not empty ( <>"" ) to keep the cell borders based on the selection.

 

[hagia_sofia]

Hi rasika_99,

This will do what you need, just add this formula in to cell D6 only. Formula will list all the matches from cell D5 downwards.

Also the formula will remove any duplicates.

PS change the 'Sheet5' to the name of your sheet and the refereces accordingly.

=UNIQUE(FILTER(Sheet5!$B:$B,Sheet5!$A:$A=$D$3))

My examples...
View attachment 116343View attachment 116345View attachment 116346

Also if you conditional formatting to column D, to add border if cell is not empty ( <>"" ) to keep the cell borders based on the selection.

Hello @sxhall, if the profile information is correct the formula @rasika_99 is looking for should be for Excel 2007...

 

[sxhall]

G

Hello @sxhall, if the profile information is correct the formula @rasika_99 is looking for should be for Excel 2007...

Good spot, will have to employ some more thinking if that is the case...!

 

[SunnyAlv]

I don't know what formulas can be used in excel 2007, but try this, hopefully all the functions can support excel 2007.

=IFERROR(INDEX(B:B,SMALL(IF(A:A=F3,ROW(B:B),""),ROW(A1:A10))),"")

 

[Fluff]

hopefully all the functions can support excel 2007.

Although those functions re in 2007, but only 2021 & 365 support spill ranges, so it will need to be changed to a drag down formula.

 

[felixstraube]

Although those functions re in 2007, but only 2021 & 365 support spill ranges, so it will need to be changed to a drag down formula.

Won't it work if you select the result range before entering the formula? I don't have 2007 anymore, but I think it might work.

 

[Fluff]

Yes but you would need to know how many rows were required. IMO it's better & simpler to just adjust the formula so it can be dragged down.

 

[felixstraube]

Fair enough. You also need to know how many rows you have to drag down. But, adjusting the number of rows for the result is easier dragging down I guess.