Excel Select Visible Cells How To Lookup Information

For a better experience, please enable JavaScript in your browser before proceeding.

Excel Select Visible Cells How To Lookup Information

I am trying to figure out how to lookup information in a filtered table, returning only visible values.

For example, lets say that the data I want to use for the lookup is in cell A1, the lookup data is in cells A3:A10, and the data I want to return is in cells B3:B10.

A normal lookup function would be Vlookup(A1,A3:B10,2,false).

But if I then autofilter the data in A3:B10 such that some rows are hidden, I only want the vlookup function to return a value if the data from A1 is also in a VISIBLE cell in A3:A10, otherwise it should return an error.

I would prefer to avoid custom functions or other use of VBA if possible.

Just out of curiosity, why wont it work to combine those two formulas into one, like so: VLOOKUP( SUBTOTAL(103, A3), IF(C3:C10, A3:B10), 2, FALSE) I tested that, and it fails.

Take a look at this screenshot: This is a sample of my data set, unfiltered.

You can see that each line lists product sales and company sales, and that there multiple products per company.

So that I can get a sum of total company sales without any duplication, cell F9 contains: IF(ISERROR(VLOOKUP(D9, IF(G8:G8,D8:D8), 1, FALSE)),D9,0) This is entered as an array formula.

It checks to see whether the company sales figure has appeared in previous rows, and if not, displays the company sales number.

But when I try to combine them into: IF(ISERROR(VLOOKUP(SUBTOTAL(103, D9), IF(G8:G8,D8:D8), 1, FALSE)),D9,0) It returns a result on every line, instead of just the first visible unique line.

It works with your 2-column solution, though, which is what really matters.

The criteria for your formula is in cell A1 Range C4:C10 uses the helper formula suggested by the guys above, and gives us our third column in the range.

The formula I have placed in cell D1 says the following: IF(VLOOKUP(A1,A3:C10,3,FALSE)1,VLOOKUP(A1,A3:B10,2,FALSE),Not visible or not found) A FALSE statement at the end of your VLOOKUP should always be included if you only want to return an EXACT match, if you dont use it then excel will return the next nearest match Im sure you can see why, when working with numeric or financial data, that the next nearest can be a baaaad thing.

In cell A2 if the value A then allow both positive and negative integers.

I have attached sample data, i need someone to help me convert the left side to how the data looks on the right.

Excel For Mac Select Visible Cells