Vlookup using visible cells only

[gurs]

Hoping there's a MrExceler out there who can help me. I am trying to figure out how to lookup information in a filtered table, returning only visible values.

For example, let's 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've done a bunch of searching, but can't find any solution. I would prefer to avoid custom functions or other use of VBA if possible. All help greatly appreciated. Thanks.

 

[shg]

You could do

=VLOOKUP(A1, IF(C3:C10, A3:B10), 2, FALSE)

... confirmed with Ctrl+Shift+Enter, where C3 and down contains the formula

=SUBTOTAL(103, A3)

 

[gurs]

Fantastic! Thanks so much.

Just out of curiosity, why won't 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.

 

[shg]

What is the value being looked up in that formula?

 

[gurs]

It is a number (for example: 1,247,319,522).

 

[shg]

Right. But in YOUR formula, what is being looked up?

=VLOOKUP(SUBTOTAL(103, A3), IF(C3:C10, A3:B10), 2, FALSE)

 

[Domenic]

Here's another way...

=VLOOKUP(A1,IF(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-ROW(A3),0,1)),A3:B10),2,FALSE)

...confirmed with CONTROL+SHIFT+ENTER.

 

[gurs]

That one didn't work for me. 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(G$8:G8,D$8: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. This way, I get each company's sales displayed only once. Cell G9 contains:

=SUBTOTAL(103, D9)

Now take a look at this screen shot, which shows the same list but filtered for products over 500,000:



In order for cell F6 to return a correct result, cell F5 needs to sum only company sales for company's which have visible rows. Which is where your formulas for columns F and G come in handy. But when I try to combine them into:

=IF(ISERROR(VLOOKUP(SUBTOTAL(103, D9), IF(G$8:G8,D$8: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!

 

[shg]

This formula won't work, as I was trying to explain:

=IF(ISERROR(VLOOKUP(SUBTOTAL(103, D9), IF(G$8:G8,D$8:D8), 1, FALSE)),D9,0)

Use either Domenic's, without the helper column, or the formula I gave you previously, with the helper column.

 

[Chatham286]

I know this thread has been here a while ... and is probably stale ... but just in case anyone else got this far and then couldn't get either of the above to work (I couldn't) .... here's what i did.

You have your reference data with your key or index on the far left. Lets assume it's in the range A4:B10 - Column B, in this case column 2, has the data you want to return only if it's visible.

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 don't use it then excel will return the next nearest match I'm sure you can see why, when working with numeric or financial data, that the next nearest can be a baaaad thing!!