Hello,
I am trying to match column A, in sheet L2 against, column b, c, or d, in sheet L1 and then return the corresponding values FN, LN, Dept in sheet L2.
Sheet L2, columns b, c, d is the desired result.
I think it requires a combination if(or(isnumber(match(.... with index etc.
Thanks in advance.
In cell B2:
You can drag that over and down.
Please Login or Register to view this content.
This will still error out if it doesn't find a match, so you MIGHT want it to do that, so you know, or you might want to have some other error handling.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
thanks miketron, worked perfectly.
to put error handling, if the value comparing to is not listed, put a blank, would that be
INDEX('L1'!E:E,IFERROR(IFERROR(MATCH('L2'!$A2,'L1'!$B:$B,0),MATCH('L2'!$A2,'L1'!$C:$C,0)),MATCH('L2'!$A2,'L1'!$D:$D,0))),"") ?
I would wrap the entire thing with an IFERROR like so.
Please Login or Register to view this content.
Thank You MikeTron!
VegasL,
Please take the time to update your profile. Show what version(s) of Excel you are using. Many members tailor formulas with that in mind.
Thanks,
Dave
In the meantime here's another way.
Formula:
Please Login or Register to view this content.
Last edited by FlameRetired; 05-26-2016 at 08:13 PM.
Dave
An afterthought.
Try array-entering this in B2, fill down and across.
Formula:Please Login or Register to view this content.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks