标题: Excel Question- Three Way Lookup [打印本页] 作者: ogoluwa 时间: 2011-10-4 01:46 标题: Excel Question- Three Way Lookup
I have 2 employees with the same name and code from Table1 and I need to get their salary from Table2.
One unique identifier is their date of joining . But there is slight difference in that value between table 1 and table 2.
I was thinking of forming an array of all the values and then based on the smallest difference in the date of joining and the small function getting their salary. But its not working.
This is a bit theoretical, but is there anyway to do the 3 way vlookup?作者: fishmarket 时间: 2011-10-4 01:49
To add the key thing is that there is slight difference in date of joining between the two dates. Else a three way lookup could have been easily done using Index and Match function.作者: onelife1 时间: 2011-10-4 01:51
Can you make a "dumb key" that is the catenation of name/code/join-date, and then look up via that?作者: yuoska 时间: 2011-10-4 01:53
Thanks. Checked it. But it doesnt work. I think the main issue is the slight difference in date of joining between the 2 tables. So if it is 21-Mar-2011, it is 28-Mar-2011 in another table.作者: transferpricing 时间: 2011-10-4 01:55
can you give us some dummy data to work with?作者: RoastBeef 时间: 2011-10-4 01:57
INDEX or a fancy sumproduct.作者: Andreas42 时间: 2011-10-4 01:59
stupid machine..
Edited 1 time(s). Last edit at Saturday, April 16, 2011 at 04:20PM by adehbone.作者: giorgio10 时间: 2011-10-4 02:06
jcole21 Wrote:
-------------------------------------------------------
> You can't come up in analyst forum talking excel
> and three ways without posting some pics or screen
> shots...
Yes, but there is sime confusion about what kind of lookup; I'm assuming that the OP wants a three-way HLOOKUP.作者: sameeragarwal 时间: 2011-10-4 02:08
If(and(employee name,one of the joined dates),Actual salary criteria for this employee,and(employee name,other joined date),Actual salary criteria for this employee,vlookup(...))
or
You add a new column at the beginning which combines the employee name and joined date and then do a lookup based on that column.作者: noel 时间: 2011-10-4 02:12
jcole21 Wrote:
-------------------------------------------------------
> You can't come up in analyst forum talking excel
> and three ways without posting some pics or screen
> shots...
I logged in just to quote this.
Gold clap and hat tip good sir.
___________________________________________________
ChickenTikka Wrote:
-------------------------------------------------------
> Being Born Wealthy > Being Jewish or WASPY > Born
> Pretty > Top 5 MBA > CFA > Avg MBA > Born middle
> class > Born lower class > Born in crack house >
> Born middleclass in Asia and working in IT but
> looking to switch to buyside
