Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Hi All
This vlookup formula is to be located inFinancialsO3 I have a Date on work sheet €˜FinancialsU2.This date will always be in €˜FinancialsU2. I want to use this date in a vlookup function as my lookup value I want to look up in worksheet €˜financial itininary column c. the dates on this worksheet start appearing in C475 advances on a daily every 49TH row. When it is found the value I want to return is in column G. This figure first appears in G479 and grows in value every 49th row. I cannot get my head round the formula. Hope someone can help Stew |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Hi
try this in O3 =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$475:$G$5000,5,0)),"",VLOOKUP(U2,'Fin ancial itininary'!$C$475:$G$5000,5,0)) you may need to expand your range of lookup table -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Hi All This vlookup formula is to be located inFinancialsO3 I have a Date on work sheet €˜FinancialsU2.This date will always be in €˜FinancialsU2. I want to use this date in a vlookup function as my lookup value I want to look up in worksheet €˜financial itininary column c. the dates on this worksheet start appearing in C475 advances on a daily every 49TH row. When it is found the value I want to return is in column G. This figure first appears in G479 and grows in value every 49th row. I cannot get my head round the formula. Hope someone can help Stew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Dear Francis
Thank You. This gets me to the G column on the same Row as the Date in C Column, However I need To Be in The G Column+4 rows Can You adapt this? Best Stew "Francis" wrote: Hi try this in O3 =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$475:$G$5000,5,0)),"",VLOOKUP(U2,'Fin ancial itininary'!$C$475:$G$5000,5,0)) you may need to expand your range of lookup table -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Hi All This vlookup formula is to be located inFinancialsO3 I have a Date on work sheet €˜FinancialsU2.This date will always be in €˜FinancialsU2. I want to use this date in a vlookup function as my lookup value I want to look up in worksheet €˜financial itininary column c. the dates on this worksheet start appearing in C475 advances on a daily every 49TH row. When it is found the value I want to return is in column G. This figure first appears in G479 and grows in value every 49th row. I cannot get my head round the formula. Hope someone can help Stew |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Hi Stew
I am not sure that I understand your reqiurement very well. You mentoned that you data first appears in G479 and grows in value every 49th row. Can you elaborate this : " However I need To Be in The G Column+4 rows " Does it means that your data start from the 4th row of Col G? if yes, change the formula to =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$4:$G$5000,5,0)),"",VLOOKUP(U2,'Finan cial itininary'!$C$4:$G$5000,5,0)) otherwise, would you provide a sample? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Dear Francis Thank You. This gets me to the G column on the same Row as the Date in C Column, However I need To Be in The G Column+4 rows Can You adapt this? Best Stew "Francis" wrote: Hi try this in O3 =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$475:$G$5000,5,0)),"",VLOOKUP(U2,'Fin ancial itininary'!$C$475:$G$5000,5,0)) you may need to expand your range of lookup table -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Hi All This vlookup formula is to be located inFinancialsO3 I have a Date on work sheet €˜FinancialsU2.This date will always be in €˜FinancialsU2. I want to use this date in a vlookup function as my lookup value I want to look up in worksheet €˜financial itininary column c. the dates on this worksheet start appearing in C475 advances on a daily every 49TH row. When it is found the value I want to return is in column G. This figure first appears in G479 and grows in value every 49th row. I cannot get my head round the formula. Hope someone can help Stew |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Hi Francis
as an example, if the date is found in Financial Itininary C6257 I want what is displayed in O3 to be Financial Itininary G6261.At the moment Your Formula Displays G6257 A49 Rows on if the date is found in Financial Itininary C6306 i want what is displayed in O3 to be Financial Itininary G6310 At the moment Your Formula Displays G6306 Hope this makes it clearer Best Stew "Francis" wrote: Hi Stew I am not sure that I understand your reqiurement very well. You mentoned that you data first appears in G479 and grows in value every 49th row. Can you elaborate this : " However I need To Be in The G Column+4 rows " Does it means that your data start from the 4th row of Col G? if yes, change the formula to =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$4:$G$5000,5,0)),"",VLOOKUP(U2,'Finan cial itininary'!$C$4:$G$5000,5,0)) otherwise, would you provide a sample? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Dear Francis Thank You. This gets me to the G column on the same Row as the Date in C Column, However I need To Be in The G Column+4 rows Can You adapt this? Best Stew "Francis" wrote: Hi try this in O3 =IF(ISNA(VLOOKUP(U2,'Financial itininary'!$C$475:$G$5000,5,0)),"",VLOOKUP(U2,'Fin ancial itininary'!$C$475:$G$5000,5,0)) you may need to expand your range of lookup table -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "stew" wrote: Hi All This vlookup formula is to be located inFinancialsO3 I have a Date on work sheet €˜FinancialsU2.This date will always be in €˜FinancialsU2. I want to use this date in a vlookup function as my lookup value I want to look up in worksheet €˜financial itininary column c. the dates on this worksheet start appearing in C475 advances on a daily every 49TH row. When it is found the value I want to return is in column G. This figure first appears in G479 and grows in value every 49th row. I cannot get my head round the formula. Hope someone can help Stew |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
.. G Column+4 rows
To extract from a row "below" what is matched, try an index/match - it allows you to adjust the "MATCH" easily Hence in O3, try this: =INDEX('Financial Itininary'!G:G,MATCH(U2,'Financial Itininary'!C:C,0)+4) which adjusts for the return from col G to be + 4 rows below the match If you need an error trap, use an IF(ISNA .. trap on the MATCH, indicatively like this: =IF(ISNA(MATCH(..)),"",INDEX(..)) voila? celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Thank you max,
and Francis "Max" wrote: .. G Column+4 rows To extract from a row "below" what is matched, try an index/match - it allows you to adjust the "MATCH" easily Hence in O3, try this: =INDEX('Financial Itininary'!G:G,MATCH(U2,'Financial Itininary'!C:C,0)+4) which adjusts for the return from col G to be + 4 rows below the match If you need an error trap, use an IF(ISNA .. trap on the MATCH, indicatively like this: =IF(ISNA(MATCH(..)),"",INDEX(..)) voila? celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Welcome, Stew
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "stew" wrote in message ... Thank you max, and Francis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
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 | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |