Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup ranges
hi,
my next question is : I want to vlookup a value in a range say : j1:d500 i.e. 0020091 is the lookup value but on the worksheet the lookup value is in j3 and the return value is in d20 how do I get this array to work I have tried alsorts but cannot succeed. anyone help here please thanks steve |
#2
|
|||
|
|||
Hi Steve
vlookup is designed to look up a value in the leftmost column of the table and return a value from the same row from another column in the table, if the value you're looking up is in row 3 of your lookup_table and the value you want to return is in row 20 then you can't use the VLOOKUP function to do it. To determine a possible solution we need to know the relationship between the lookup_value in the table (column J) and the return value (column D) are they always 17 rows apart? if not, how do you know where what you're looking for in column J is located in column D? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "steve alcock" wrote in message ... hi, my next question is : I want to vlookup a value in a range say : j1:d500 i.e. 0020091 is the lookup value but on the worksheet the lookup value is in j3 and the return value is in d20 how do I get this array to work I have tried alsorts but cannot succeed. anyone help here please thanks steve |
#3
|
|||
|
|||
Hi Julie,
on banking I get a cheque value number from another cell automatically, this is the start of my vlookup; in the next column I want to vlookup this value on another sheet, same book, which is located in JX and the value I want is in dX the cheque number and the value are on the same line and always JX and DX any further help would be appreciated regards steve -----Original Message----- Hi Steve vlookup is designed to look up a value in the leftmost column of the table and return a value from the same row from another column in the table, if the value you're looking up is in row 3 of your lookup_table and the value you want to return is in row 20 then you can't use the VLOOKUP function to do it. To determine a possible solution we need to know the relationship between the lookup_value in the table (column J) and the return value (column D) are they always 17 rows apart? if not, how do you know where what you're looking for in column J is located in column D? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "steve alcock" wrote in message ... hi, my next question is : I want to vlookup a value in a range say : j1:d500 i.e. 0020091 is the lookup value but on the worksheet the lookup value is in j3 and the return value is in d20 how do I get this array to work I have tried alsorts but cannot succeed. anyone help here please thanks steve . |
#4
|
|||
|
|||
Hi Steve
one option is to use the OFFSET and MATCH functions =OFFSET(Sheet2!$J$1,MATCH(A1,Sheet2!$J$1:$J$20,0)-1,-6) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "steve alcock" wrote in message ... Hi Julie, on banking I get a cheque value number from another cell automatically, this is the start of my vlookup; in the next column I want to vlookup this value on another sheet, same book, which is located in JX and the value I want is in dX the cheque number and the value are on the same line and always JX and DX any further help would be appreciated regards steve -----Original Message----- Hi Steve vlookup is designed to look up a value in the leftmost column of the table and return a value from the same row from another column in the table, if the value you're looking up is in row 3 of your lookup_table and the value you want to return is in row 20 then you can't use the VLOOKUP function to do it. To determine a possible solution we need to know the relationship between the lookup_value in the table (column J) and the return value (column D) are they always 17 rows apart? if not, how do you know where what you're looking for in column J is located in column D? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "steve alcock" wrote in message ... hi, my next question is : I want to vlookup a value in a range say : j1:d500 i.e. 0020091 is the lookup value but on the worksheet the lookup value is in j3 and the return value is in d20 how do I get this array to work I have tried alsorts but cannot succeed. anyone help here please thanks steve . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Function using Data Ranges. | Excel Worksheet Functions | |||
How do I do a VLOOKUP from 2 ranges and add the results together? | Excel Worksheet Functions | |||
Vlookup with 2 ranges in one worksheet | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions |