Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
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 --- "JeffK" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Had to tweak a bit but worked fantasitic.
thanks a bunch "Max" wrote: 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 --- "JeffK" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
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" wrote: 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 --- "JeffK" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
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" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
The tinkering worked,
Thanks a bunch "Max" wrote: 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" wrote: 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? |
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 |