Excel Question- Three Way Lookup difference, employees, function, between

ogoluwa

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

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

Can you make a "dumb key" that is the catenation of name/code/join-date, and then look up via that?

yuoska

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

can you give us some dummy data to work with?

RoastBeef

INDEX or a fancy sumproduct.

Andreas42

stupid machine..



Edited 1 time(s). Last edit at Saturday, April 16, 2011 at 04:20PM by adehbone.

giorgio10

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

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

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