# vlookup

S

#### stew

Hi All

This vlookup formula is to be located inâ€™Financialsâ€™O3

I have a Date on work sheet â€˜Financialsâ€™U2.This date will always be in
â€˜Financialsâ€™U2. 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

Hi
try this in O3

=IF(ISNA(VLOOKUP(U2,'Financial
itininary'!\$C\$475:\$G\$5000,5,0)),"",VLOOKUP(U2,'Financial
itininary'!\$C\$475:\$G\$5000,5,0))

you may need to expand your range of lookup table

--

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

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

Best

Stew

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,'Financial
itininary'!\$C\$4:\$G\$5000,5,0))

otherwise, would you provide a sample?

--

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

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

.. 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
xdemechanik
---

Thank you max,
and Francis

Max said:
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