Array VLOOKUP() into diffrent cells?

[SirMille]

Ok, so I'm using a simple VLOOKUP() to fetch some user data from an db sheet that has all info about the users. For the col_index_num parameter I use {2,3,5,9}.

Now I'd like to have all the data returned in separate cells in my working sheet. Would that be possible somehow? (Instead of having to manually write several VLOOKUP()'s with different col_index_num in separate cells.)

Any suggestions are welcome!

Thank you in advance!

 

[Aladin Akyurek]

Ok, so I'm using a simple VLOOKUP() to fetch some user data from an db sheet that has all info about the users. For the col_index_num parameter I use {2,3,5,9}.

Now I'd like to have all the data returned in separate cells in my working sheet. Would that be possible somehow? (Instead of having to manually write several VLOOKUP()'s with different col_index_num in separate cells.)

Any suggestions are welcome!

Thank you in advance!

Why not create a formula you can copy to other cells?

 

[SirMille]

Why not create a formula you can copy to other cells?

Not sure how. Any hints?

 

[Aladin Akyurek]

Not sure how. Any hints?

Example:

B2:

=VLOOKUP(A2,Table,2,0)

which can be copied down for looking up the values in A3, A4, etc.


By the way, what is your current formula?

 

[SirMille]

Example:

B2:

=VLOOKUP(A2,Table,2,0)

which can be copied down for looking up the values in A3, A4, etc.


By the way, what is your current formula?

Oh. Ofc. Simple like that.

I was trying playing around with =VLOOKUP(I6,A2:G5, {2,5,9}, FALSE)

To have a shortcut. I like skipping steps.

 

[T. Valko]

Oh. Ofc. Simple like that.

I was trying playing around with =VLOOKUP(I6,A2:G5, {2,5,9}, FALSE)

To have a shortcut. I like skipping steps.

If there was a pattern in the column index numbers then you could write the formula to increment them as the formula is copied but there is no pattern in your column index numbers 2, 5, and 9.

Note that your table array A2:G5 is only a total of 7 columns so using a column index number of 9 will result in a formula error.

 

[Aladin Akyurek]

Oh. Ofc. Simple like that.

Yes, indeed.

I was trying playing around with =VLOOKUP(I6,A2:G5, {2,5,9}, FALSE)

To have a shortcut. I like skipping steps.

A2:G5 does not have 9 columns, so that would to a #REF! error.

If you wanted the results for I6 in J6:L6, say, from columns say 2, 5, and 7 of A2:G5, we have the following choices: (a) either use the hand-coded 2, 5, and 7 directly in the formula or (b) calculate these column numbers. The following would be somewhat fancy and still considered hand-coded:

In J6 enter, copy across, and down:

=VLOOKUP($I6,$A$2:$G$5,INDEX({2,5,7},COLUMNS($J6:J6)),0)

 

[T. Valko]

Oh. Ofc. Simple like that.

I was trying playing around with =VLOOKUP(I6,A2:G5, {2,5,9}, FALSE)

To have a shortcut. I like skipping steps.

If you only have 3 lookups to do what's so bad about entering 3 formulas? They're simple short formulas:

=VLOOKUP(I6,A2:G5,2,0)

=VLOOKUP(I6,A2:G5,5,0)

=VLOOKUP(I6,A2:G5,7,0)