Vlookup

J

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, A1:D100

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, A1:D500.

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!A1:D500,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.
 
M

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:D$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
 
J

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

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

JeffK

The tinkering worked,

Thanks a bunch

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top