vert.search with indirect error.

I have no idea what I am doing wrong.
I am using the following ;

=VERT.ZOEKEN(Q3;INDIRECT("'"&A6&"'"&"!A1:M90");9;0)

Where Q3 contains the search criterea, Indirect is the search tab and range ( I assume) where in the sheet A6 contains the name of the respective tab. A1:M90 is the range it should be searching eventually displaying 9.

What am I doing wrong? Ill appreciate any help and yes, I did go through google sessions, forum browsing etc. etc. There are questions in the same trend but nothing specific. This is my first time working with INDIRECT in a vert. search so I am not that good at it :p.

Thanks in advance!

Hello,

what does the formula return?

N/A means that the value in Q3 cannot be found in the lookup table
REF means that the lookup table reference is not valid.

I don't know how these error values present in the Dutch version of Excel.

What error do you get?

Can you post a small sample file for inspection?

cheers,

It gives me #N/B for most of the lines, there are a few which display 0. I have rebuild the code to the following:


=VERT.ZOEKEN($Q$2;INDIRECT("'"&A10&"'!A:L");9)

I have manually tried to ctrl+F in tabs and the exact notation of cell Q3 is avaible in the specific (#N/B which means not avaible) tab.

it seems to me though that the sells in A which contain 1A for example, a number + letter it does give me the value I am looking for. The cells that contain numbers only do not give back the info.

if this isnt of any help I can rebuild something and post it here.

EDIT 1: All which display 0 are tabs with NUMBERXLETTERX name. The value is correct. The format of all tabs is the same only the number of rows can vary (why I need vert. search) I think the problem is something with a tab name only consisting of numbers.

I really hope anyone knows the solution, as usual I have the feeling its too easy

In general, the formula is correct. The problem seems to be with the values and whether or not there is actually a match for Q3 (or Q2) in the data table.

See here for some ideas about what you need to check

http://www.experts-exchange.com/Soft...-Not-Work.html

Or, post a sample of your file for inspection.

cheers,

Here , hope its helpfull. Thanks for your continued help and effort! greatly appreciated.

Don't use whole column reference with Vlookup (or VERT.ZOEKEN )

Limit the lookup table to only the populated rows.

The rows are not the problem if you get N/A.

Post the data sample.

OK.

In the English Excel version, the formula in your spreadsheet is this

=VLOOKUP($A$2,INDIRECT("'"&A5&"'!B:L"),3)

The lookup range starts in column B and you return the third column (of the lookup range!!!) = column D for a match. But column D is empty in the sheet with the data. The empty cell will be returned as a 0 value.

So, what you really need to return the value in column C of the data table is

=VLOOKUP($A$2,INDIRECT("'"&A5&"'!B:L"),2,FALSE)

Also, if you want to return an exact match, make sure to include the fourth parameter of the Vlookup function and set it to False or 0. If you don't do that, Excel will return the value of the next smallest match in a table that is sorted ascending (small to large) in column B. If the table is not sorted, then the results will appear, uhmm, funny.

The formula in row 12 returns the Ref! error, because there is no sheet called 9879A in the workbook.

See attached. When you open it, the workbook will again show the Dutch version of the functions and the semicolon instead of the comma as the delimiter in the formula.

Does that help?

cheers,

Mitch, you need to understand that the lookup range in Vlookup is anchored in the first column of the lookup table.

vlookup(A1,B:L,2,0)

will return the value of the second column in the table that starts in column B. So the value returned will be from column C.

vlookup(A1,B:L,3,0)

will return the value of the third column in the table that starts in column B. So the value returned will be from column D.