Vlookup

[JeffK]

I have two tables which both include column headings: Branch, Account #, Date.

Table 1 also has an "account size" ($) heading and the date column indicates
when the account was setup. Table is sheet1, A1100

Table 2 has "risk rating" heading and the date column indicates when the
risk rating was calculated (not account setup as with Table 1). Table is
sheet2, A1500.

The issue is an account will only appear in Table 1 once, where it could
appear 5 or 6 times in Table 2 depending how many times the risk rating was
calculated. I was using a Vlookup in Column E on sheet 1 and it would bring
the first risk rating that was listed for the account.

=VLOOKUP($A1,Sheet2!A1500,4,FALSE)

What I would like to do is have the lookup bring back the entry that was
completed on or just prior to the account being setup (column C).

Your help is appreciated.

 

[Max]

An index/match would generally provide greater flexibility for multiple
criteria extractions (easier to understand what's happening as well)

Assuming Col C = real dates (in both Sheet2 and Sheet1)
In Sheet1,
Try this in E1, normal ENTER will do
=INDEX(Sheet2!D$1$100,MATCH(1,INDEX((Sheet2!A$1:A$100=A1)*(Sheet2!C$1:C$100<=C1),),0))
Adapt the ranges to suit, copy down. Verify that it gives correct results on
your test data. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[JeffK]

Had to tweak a bit but worked fantasitic.

thanks a bunch

 

[JeffK]

Hey Max,

Actually after looking a bit closer, the formula brings back the rating that
took place on or before the opening date, but if there's more than one that
fits the criteria, it will bring back the first one it comes to.

How do I change it so it only brings back the one that's occured within the
previous 30 days?

 

[Max]

Jeff,

Tinker with the criteria bit, ie this part:
...(Sheet2!A$1:A$100=A1))*(Sheet2!C$1:C$100<=C1)..

Your line/new query :

..only brings back the one that's occured within the previous 30 days?

would suggest that: TODAY()-30
is an additional, lower limit to be applied for the dates in col C,
ie an added criteria: (Sheet2!C$1:C$100>=TODAY()-30)

Hence you could try building it on like this for the criteria part:
... (Sheet2!A$1:A$100=A1))*(Sheet2!C$1:C$100>=TODAY()-30)
*(Sheet2!C$1:C$100<=C1) ..

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

 

[JeffK]

The tinkering worked,

Thanks a bunch

Jeff,

Tinker with the criteria bit, ie this part:
..(Sheet2!A$1:A$100=A1))*(Sheet2!C$1:C$100<=C1)..

Your line/new query :
would suggest that: TODAY()-30
is an additional, lower limit to be applied for the dates in col C,
ie an added criteria: (Sheet2!C$1:C$100>=TODAY()-30)

Hence you could try building it on like this for the criteria part:
.. (Sheet2!A$1:A$100=A1))*(Sheet2!C$1:C$100>=TODAY()-30)
*(Sheet2!C$1:C$100<=C1) ..

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