Getting data from a cell after filtering. HELP! PLEASE

[rverdeza]

Hello everybody, if someone can assist me with this question i will greatly appreciate it!

I am currently working on an excel spreadsheet and i am having trouble getting data from a cell after i filter the information.

My data range runs from A4:G561 and when i filter i want to get the data that appears in the first row of the table.
This table has headers so the data begins in column A5.

i will need to get the values of the cells as strings and put them into another sheet in the spreadsheet. This data will be constantly changing. We have a text field to search for the zip code and we need to populate another sheet in the excel spreadsheet with the hospital name, Address, Zip code, City and state

If there is a way to just get the data and put it into another cell that's fine as well. i just need to get the data when we filter it. when row A5 becomes row A25 for example.

 

[XLearner]

Hi,

Assuming the following setup:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]CITY
[/TD]
[TD="align: center"]ZIP[/TD]
[TD][/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]City 1[/TD]
[TD="align: right"]1001[/TD]
[TD][/TD]
[TD="align: right"]1004[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]City 2[/TD]
[TD="align: right"]1002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]City 3[/TD]
[TD="align: right"]1003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]City 4[/TD]
[TD="align: right"]1004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]City 5[/TD]
[TD="align: right"]1005[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]City 6[/TD]
[TD="align: right"]1006[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]City 7[/TD]
[TD="align: right"]1007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If you filter the CITY column to display City 4 as the 1st visible value, the following formula in D5 returns its ZIP code (column #2 of the Index) ==> 1004

=INDEX($A$5:$B$11,MATCH(1,SUBTOTAL(3, OFFSET($A$5,ROW($A$5:$A$11)-ROW($A$5),0)),0),[COLOR=#008000][B]2[/B][/COLOR])

validate with Ctrl+Shift+Enter (array formula)

Regards
XLearner