| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Function
I am using the vlookup function.
How can I use the above function, so that I don't have to change the column reference (col_index_num), when the relevant lookup column is moved within the table (table_array)? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Function
Replace it with an index/match. The indexed col (return col) will auto-adjust
when you move it. Example: If you have in B2: =VLOOKUP(A2,Sheet2!A:D,3,0) replace it with this equivalent index/match: =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0)) When you move (cut n paste) Sheet2!C:C to say Sheet2!E:E the formula will auto-adjust to stay in sync, viz.: =INDEX(Sheet2!E:E,MATCH(A2,Sheet2!A:A,0)) while the vlookup stays unadjusted at: =VLOOKUP(A2,Sheet2!A:D,3,0) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "nc" wrote: I am using the vlookup function. How can I use the above function, so that I don't have to change the column reference (col_index_num), when the relevant lookup column is moved within the table (table_array)? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Function
Thanks.
What about if I want the vlookup to pick the column based on the field name? "Max" wrote: Replace it with an index/match. The indexed col (return col) will auto-adjust when you move it. Example: If you have in B2: =VLOOKUP(A2,Sheet2!A:D,3,0) replace it with this equivalent index/match: =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0)) When you move (cut n paste) Sheet2!C:C to say Sheet2!E:E the formula will auto-adjust to stay in sync, viz.: =INDEX(Sheet2!E:E,MATCH(A2,Sheet2!A:A,0)) while the vlookup stays unadjusted at: =VLOOKUP(A2,Sheet2!A:D,3,0) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "nc" wrote: I am using the vlookup function. How can I use the above function, so that I don't have to change the column reference (col_index_num), when the relevant lookup column is moved within the table (table_array)? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Function
What about if I want the vlookup to pick the column based on the field name?
That's a new query. You could try using a MATCH expression to return the col_index_num within the vlookup. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedded If Function in a Vlookup Function | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
| how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
| I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
Linear Mode
