Ho to get multiple column values in one shot thru vlookup

Copy and paste the same Vlookup() formula to all the five columns and just change the column number parameter in the other 4 columns. Copy down the formula and you are done.

Hi,

Assume that all your data in Sheet1


=VLOOKUP($A2,Sheet1!$A:$E,COLUMNS($A$1:B$1),0)

where a2 houses the partner id.

HTH

Hi Annalina,

It can be done via a query table (or similar use of SQL). Most users consider this an advanced approach and it has its own quirks. It does not use formulas on the spreadsheet and has a few good points such as speed and ability to 'lookup' closed files & databases.

Some pointers - name the ranges (this isn't strictly necessary but might be easier), headers to all fields, make sure the file has been saved at some time ever. Menu path Data, Import External Data, New Database query. Select the file and follow the wizard. You can select two tables from the wizard and create the join between them in MS Query or select one table in the wizard and add the second in MS Query (either via the menu & icons, or, by directly editing the SQL).

If you're interested, I suggest you look in help or google for more information. If you have a question on this approach, please post again in this thread.

Or, it can be done via VBA. And instead of a query table one might use ADO or alternative. Or without this SQL type approach, a different way would be via VBA (such as using arrays).

regards, Fazza

Hi,

Assume that all your data in Sheet1


=VLOOKUP($A2,Sheet1!$A:$E,COLUMNS($A$1:B$1),0)

where a2 houses the partner id.

HTH

Click to expand...

Hi Krishna

I suppose this can be done in an index(match()) alternate to vlookup()? I guess I need to replace the column number in index() with "COLUMNS()".

Am I correct?

Yes.

Hi Krishna

I suppose this can be done in an index(match()) alternate to vlookup()? I guess I need to replace the column number in index() with "COLUMNS()".

Am I correct?

Click to expand...

Hello all,

I am looking forward to implement this logic in my data and seems this does not work.

In the main worksheet "Retail_cust_mobile#" I have two columns Mobile 1 & Mobile 2 which I need to lookup and match with all the mobile numbers in another worksheet. How do I incorporate the above logic of multiple columns in this to get the desired accurate result?

I tried specifying two cell references for two columns with the & operator in the match function but that does not work either.

Any help and I would be obliged.

many thanks for your help.

Hi,

Does this

=LOOKUP(9.999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(E8211,Collated_Advisory_Email!$B$2:$D$917,3,0),VLOOKUP(F8211,Collated_Advisory_Email!$B$2:$D$917,3,0)))

format the cell

[=0]"Doesn't Exist";General

help you?

oh thank you so much, Krishna.

I have not tested this on my data yet. However, if you can please let me know how do I incorporate this into index(mtach()) rather than vlookup().

This is because I need to use this formula to do a number of different base lookups and all the field columns which I want to retrieve are on the left of the key fields in my data range.

Hi,

=LOOKUP(9.999999999E+307,CHOOSE({1,2,3},0,INDEX(Collated_Advisory_Email!$D$2:$D$917,MATCH(E8211,Collated_Advisory_Email!$B$2:$B$917,0)),INDEX(Collated_Advisory_Email!$D$2:$D$917,MATCH(F8211,Collated_Advisory_Email!$B$2:$B$917,0))))

HTH