table_array split into two sets of cells

[BigBillTolbert]

I am doing a simple VLOOKUP and it works fine:

I want to do the same lookup, however the table_array is split into two sets of cells.

I tried to use =VLOOKUP(A5,A8:B9 C10:D11,2) setting it to A8:B9 and C10:D11 and got an error. I'm not sure if you can have a table_array that is split up like I am showing or if I just have bad syntax.

I know I can move the rows around or create some formulas to make it look like one contagious set of data, but I am trying to read the source of the data as it comes to me.

Thanks

 

[Fluff]

You could try

=XLOOKUP(A5,A8:A9,B8:B9,XLOOKUP(A5,C10:C11,D10:D11))

but it would be better to get the data sorted out.

 

[BigBillTolbert]

You could try

=XLOOKUP(A5,A8:A9,B8:B9,XLOOKUP(A5,C10:C11,D10:D11))

but it would be better to get the data sorted out.

Thanks for the reply. The data is clean as there will only be one match within both datasets. I did look at your approach and was able to make it work and then the issue moved to what if I have 12 data sets like one for each month.

I thought I could do something within the table_array argument specifically I might be able to keep it to one formula vs 12 formulas that are netted out to get the one formula that has the right piece of data.

 

[Fluff]

the issue moved to what if I have 12 data sets like one for each month.

That is why I said it would be better to get the data sorted out.
Do you have the Hstack function yet?

 

[BigBillTolbert]

VSTACK() did the trick - thanks for the pointer to HSTACK()

 

[BigBillTolbert]

I am doing a simple VLOOKUP and it works fine:

View attachment 76569

I want to do the same lookup, however the table_array is split into two sets of cells.

View attachment 76570

I tried to use =VLOOKUP(A5,A8:B9 C10:D11,2) setting it to A8:B9 and C10:D11 and got an error. I'm not sure if you can have a table_array that is split up like I am showing or if I just have bad syntax.

I know I can move the rows around or create some formulas to make it look like one contagious set of data, but I am trying to read the source of the data as it comes to me.

Thanks

The correct answer is to use the VSTACK() function.

=VLOOKUP(A5,VSTACK(A8:B9,C10:D11),2)

 

[Fluff]

Glad you sorted it & thanks for letting us know.