vlookup

[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

 

[Francis]

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

--
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]

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]

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?

--
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]

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

 

[Max]

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

 

[stew]

Thank you max,
and Francis

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

 

[Max]

Welcome, Stew
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik