VLOOKUP

[Miguel_Zafiro]

The formula VLOOKUP consists on this:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

Syntax
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Now, please take a look at the image that i am attaching

My native language is spanish, so the proyect i am building is in spanish

But if you take a look at the image i attached, you can see that the cell that's selected is AV27

And it says:

=SI(H27="",BUSCARV(I27,$AK$26:$AR$46,8,FALSO),"")

Which in English would be:

=IF(H27="",VLOOKUP(I27,$AK$26:$AR$46,8,FALSE),"")

There's no need for you to know what columns H and I have.

As you can see in the image

$AK$26:$AR$46 is what i called "TABLA 3", which would be "TABLE 3" in English

But as you can see, the "TABLE 3" is in fact 3 different mini tables,

Table 1 - AK:AM
Table 2 - AO:AP
Table 3 - AR

But in my formula of AV27, i treated those 3 tables, as a single table.

Fortunately, the formula functioned fine. I treated those 3 mini tables as a single table of 8 columns, and even though I did that, and even though there are empty columns, such as AN and AQ, the formula functions perfectly fine.

My question is:

Can I use a VLOOKUP formula where my second argument is 3 different mini tables which i am treating as a single table? Can the formula still work fine? Can i rest in peace that nothing will go wrong? Is that theorically valid?

[#2]

Hi @Miguel_Zafiro, 

If you want vlookup to search through 3 different table I'd use something like this...

=ifna(vlookup(value,table1,column index),ifna(vlookup(value,table2,column index), vlookup(value,table3,column index)))

See attached screenshot for example.

[#3]

@Miguel_Zafiro 

You asked: Can I use a VLOOKUP formula where my second argument is 3 different mini tables which i am treating as a single table? Can the formula still work fine? Can i rest in peace that nothing will go wrong? Is that theorically valid?

To which I'd reply that in the example you give (the image file) it really is only one table. The fact that you conceptually think of it as three doesn't change the fact that it is a single set of columns and rows with the first column serving as the basis for using VLOOKUP with offsets going to any of the columns. You may conceive of them as separate mini-tables, but they're not seen that way by Excel.

Now, that said, I will point out that a true Excel Table would not have the blank rows you've incorporated, perhaps as a way to create mini-tables, or mini-subsets, in the vertical dimension. A true, and most error-free, Excel Table would be continuous rows of data, with no blank rows. You can have blank cells (if no data is available or some such) for any given row and column intersection.

If you're trying to differentiate between different entities by leaving blank rows, I'd suggest adding a column to take care of that differentiation. When it comes time to summarize through Pivot Tables, etc., you'll have a lot easier time.