Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp
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 On Jul 23, 2:54 pm, Nabil wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp
Assume columns A:D are your reference columns and E is your data column:
=INDEX(E2:E100,MATCH(1,(A2:A100=ref1)*(B2:B100=ref 2)*(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 "Nabil" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp
Hi
Tanks for both of you, its working now. Best Regards Toppers a écrit : Assume columns A:D are your reference columns and E is your data column: =INDEX(E2:E100,MATCH(1,(A2:A100=ref1)*(B2:B100=ref 2)*(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 "Nabil" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |