VLookUp

N

Nabil

HelloI'm new in Excel macro and functions, so I have a sheet with a
several columns (4) columns.
I have to find the referneces in the 4 columns and get the
corresponding data in the fifth coolumn.
Is it possible to do it with the VLookUp? Or there are another way?
Regards
 
P

Pete_UK

You could use VLOOKUP, but you will have to concatenate your four
columns into one in your main table. Assume this is on Sheet1 - just
insert a new column E and enter this formula in E1:

=A1&B1&C1&D1

and copy this down the new column E. Then your VLOOKUP formula in the
other sheet would look something like:

=VLOOKUP(A3&B3&E3&D3,Sheet1!E$1:F$1000,2,0)

where I am assuming you enter data values on row 3 of your second
sheet and that you have up to 1000 different values in your main table
on Sheet1.

Hope this helps.

Pete
 
T

Toppers

Assume columns A:D are your reference columns and E is your data column:

=INDEX(E2:E100,MATCH(1,(A2:A100=ref1)*(B2:B100=ref2)*(C2:C100=ref3)*(D2:D100=ref4),0))

Enter with Ctrl+Shift+Enter (you will get {} round the formual if entered
correctly)

where ref1 to ref4 are your values to compared.

Put these in cells:

e.g. in X1 to X4

=INDEX(E2:E100,MATCH(1,(A2:A100=X1)*(B2:B100=X2)*(C2:C100=X3)*(D2:D100=X4),0))

HTH
 
Top