V-Lookup Question

[Guzzlr]

Hello All
I have the following V-Lookup:

=VLOOKUP($B$28,'[Data Base_Current State.xlsm]Speciality '!$B:$N,2,FALSE)

this of course is working. However, it will only pull back the data in col_index_num 2.
What I'm looking for, is to pull back all data in the row of 28, to where ever the data ends in the columns. so if col_index_num is Column C then row 28 would pull data from Column C:J for this example.
However, row 29 may have data from C:L...each row has a different length of data to columns.
Is this possible?
Thanks for the help

 

[jasonb75]

Please update your account details to show which version of excel you are currently using. With office 365 dynamic arrays this is a simple task, with older versions it can still be done but needs a more creative formula.

 

[Guzzlr]

With office 365 dynamic arrays this is a simple task,

so the computer I'm currently on, and asking the V-LOOKUP question is Office 365. However, my other computers have excel 2019 and some older...one of my computers I'm still using 2010.
Thanks for the help.

 

[jasonb75]

In that case you will need to use the long method, which means entering the formula into the first cell then dragging it right far enough to cover all possibilities. So if the maximum range ised will be columns B:L (11 columns) then you need to drag the formula right for 10 columns. As column B is not being returned as a result it is not necessary to include that in the count.

=IF(INDEX('[Data Base_Current State.xlsm]Speciality '!C:C.MATCH($B$28,'[Data Base_Current State.xlsm]Speciality '!$B:$B,0))="","",INDEX('[Data Base_Current State.xlsm]Speciality '!C:C.MATCH($B$28,'[Data Base_Current State.xlsm]Speciality '!$B:$B,0)))

 

[Guzzlr]

Super...Thank you!