|
Vlookup...
Hello Esteemed Excel Community...
I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
You could use something like this:
=IF(ISERROR(VLOOKUP(B1,hours!A1:E5,5,FALSE))=FALSE ,VLOOKUP(B1,hours!A1:E5,5,FALSE),IF(ISERROR(VLOOKU P(B1,hours!B1:E5,4,FALSE))=FALSE,VLOOKUP(B1,hours! B1:E5,4,FALSE),IF(ISERROR(VLOOKUP(B1,hours!C1:E5,3 ,FALSE))=FALSE,VLOOKUP(B1,hours!C1:E5,3,FALSE),"no record"))) HTH, Elkar "NWO" wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Hi!
Since both sets of data contain the persons name why not just lookup the name? The userid could be any one of three but the name is the same, right? Biff "NWO" wrote in message ... Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Helo Biff.
Because the names are almost always spelled and formatted idfferently, or I would have already followed this path. Mark :) "Biff" wrote: Hi! Since both sets of data contain the persons name why not just lookup the name? The userid could be any one of three but the name is the same, right? Biff "NWO" wrote in message ... Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Assuming that A2:E10 contains your data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(E2:E10,MATCH(TRUE,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)) 0,0)) ....where G2 contains the UserID of interest. Hope this helps! In article , NWO wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Hello Elkar.
I think you're on the right track, but the formula didn't quite work, proabably because I need to clear up my explanation: I noticed that your formula has a reference to ...A1:E5...B1:e5, etc. I am basing my premise on one row that contains UserID#1 in the first cell of the row (which is usally the correct ID), UserID#2 in the second cell of the same row, which is a different ID but for the same name, UserID#3 in the third cell of the same row, Name in the fourth cell of the same row, and Production Hours in the fifth cell of tyhe same row. So, basically, I guess what I am asking is how to first check the first UserID to see if this is a match, if so, then take the Production Hours value, and done. If the first UserID is not a match, then check to see if the second UserID is a match, etc. The reason for this is because the HOURS worksheet only contains unique names, whereas the SOURCE worksheet can have up to three different UserIDs for the same name, and these names are listed in consecutive rows, one for each UserID. Again, I sure appreciate any help on this matter. Thank you again Elkar. Mark :) "Elkar" wrote: You could use something like this: =IF(ISERROR(VLOOKUP(B1,hours!A1:E5,5,FALSE))=FALSE ,VLOOKUP(B1,hours!A1:E5,5,FALSE),IF(ISERROR(VLOOKU P(B1,hours!B1:E5,4,FALSE))=FALSE,VLOOKUP(B1,hours! B1:E5,4,FALSE),IF(ISERROR(VLOOKUP(B1,hours!C1:E5,3 ,FALSE))=FALSE,VLOOKUP(B1,hours!C1:E5,3,FALSE),"no record"))) HTH, Elkar "NWO" wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Thank you Domenic.
Your potential solution is way to complex for me. I think I'll just stick with an ISERROR..VLOOKUP solution because I can understand this. Thank you. Mark :) "Domenic" wrote: Assuming that A2:E10 contains your data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(E2:E10,MATCH(TRUE,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)) 0,0)) ....where G2 contains the UserID of interest. Hope this helps! In article , NWO wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
|
Vlookup...
Thank you Gilbert - solution worked.
Mark :) "Gilbert De Ceulaer" wrote: Sorry, mistake it should be : =IF(ISERROR( SETV(VLOOKUP(B1;hours!A1:D5;4;FALSE);1 ))=FALSE;GETV(1); IF(ISERROR( SETV(VLOOKUP(B1;hours!B1:D5;3;FALSE);2 ))=FALSE;GETV(2); IF(ISERROR( SETV(VLOOKUP(B1;hours!C1:D5;2;FALSE);3 ))=FALSE;GETV(3);"no record"))) |
Vlookup...
Thank you Elkar.
After further review of your solution, it does indeed work. Mark :) "Elkar" wrote: You could use something like this: =IF(ISERROR(VLOOKUP(B1,hours!A1:E5,5,FALSE))=FALSE ,VLOOKUP(B1,hours!A1:E5,5,FALSE),IF(ISERROR(VLOOKU P(B1,hours!B1:E5,4,FALSE))=FALSE,VLOOKUP(B1,hours! B1:E5,4,FALSE),IF(ISERROR(VLOOKUP(B1,hours!C1:E5,3 ,FALSE))=FALSE,VLOOKUP(B1,hours!C1:E5,3,FALSE),"no record"))) HTH, Elkar "NWO" wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
If you're willing to explain it, I'm willing to try to understand it. It
does seem to work. -- Kevin Vaughn "Domenic" wrote: Assuming that A2:E10 contains your data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(E2:E10,MATCH(TRUE,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)) 0,0)) ....where G2 contains the UserID of interest. Hope this helps! In article , NWO wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Why not just:
=INDEX(E2:E10,MATCH(1,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)),0)) Or even: =INDEX(E2:E10,MATCH(1,MMULT(--(A2:C10=G2),{1;1;1}),0)) Biff "Domenic" wrote in message ... Assuming that A2:E10 contains your data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(E2:E10,MATCH(TRUE,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)) 0,0)) ...where G2 contains the UserID of interest. Hope this helps! In article , NWO wrote: Hello Esteemed Excel Community... I thought I knew all about the VLOOKUP function, but... Here's my deal...on one worksheet called SOURCE, I have two columns of data, named UserID and Name. On another worksheet in the same file, named HOURS, I have 5 columns named UserID1, UserID2, UserID3, Name, and Production Hours. The reason for the three UserID* columns is because we are still using a BTrieve system and some users have up to three UserIDs to identify the same name. So, what I am trying to do is to capture the Production Hours data for a given employee based on the single UserID in the SOURCE worksheet, and compare this value to the three UserID cells on the HOURS worksheet fore a match, and then copy Production Hours value. The names should match with a given UserID with slight differences. So, I know a =VLOOKUP formulasay in cell b3 on the SOURCE worksheet, would work in the form =VLOOKUP(b1,HOURS!b1:b5,FALSE) would yield the proper value if I was just concerned with comparing one unique UserID to another unique UserID, but how do I accomplish this with testing which one of the UserID values matches, and then fetching the value if there is a match? Can this be done with the VLOOKUP function? Thank you in advance. Mark :) |
Vlookup...
Let's assume we'd like to find the production hours for UserID 'E' in
the following table... UserID1 UserID2 UserID3 Name ProdHrs A B C John 40 D E F Joe 35 X Y Z Jane 45 ....and that we have the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(E2:E4,MATCH(TRUE,MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0))0 ,0)) Here's how it breaks down... A2:C4="E" evaluates to: {FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FA LSE} --(A2:C4="E") evaluates to: {0,0,0;0,1,0;0,0,0} Note that the double negative coerces TRUE and FALSE to 1 and 0, respectively. COLUMN(A2:C4) evaluates to: {1,2,3} COLUMN(A2:C4)^0 evaluates to: {1,1,1} TRANSPOSE(COLUMN(A2:C4)^0) evaluates to: {1;1;1} MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0)) evaluates to: {0;1;0} MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0))0 evaluates to: {FALSE;TRUE;FALSE} If you're not familiar with matrix multiplication, have a look at the following link... http://www.purplemath.com/modules/mtrxmult.htm Now that you've seen how the formula breaks down, here's how the evaluation takes place... =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},TRANSPOSE({1,2,3}^ 0))0,0)) =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},TRANSPOSE({1,1,1}) )0,0)) =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},{1;1;1})0,0)) =INDEX({40;35;45},MATCH(TRUE,{0;1;0}0,0)) =INDEX({40;35;45},MATCH(TRUE,{FALSE;TRUE;FALSE},0) ) =INDEX({40;35;45},2) ....which returns 35. Hope this helps! In article , Kevin Vaughn wrote: If you're willing to explain it, I'm willing to try to understand it. It does seem to work. -- Kevin Vaughn |
Vlookup...
In article ,
"Biff" wrote: Why not just: =INDEX(E2:E10,MATCH(1,MMULT(--(A2:C10=G2),TRANSPOSE(COLUMN(A2:C10)^0)),0)) Actually, I prefer my syntax. It allows for human error. For example, if by mistake the same UserID is entered a second time on the same row, my formula will still return a correct answer. Of course, with only 3 UserID columns, an error like this is not very likely. Or even: =INDEX(E2:E10,MATCH(1,MMULT(--(A2:C10=G2),{1;1;1}),0)) Sure, since in this case there's only 3 UserID columns and they're not likely to expand. And, I guess the added bonus is that there's no need to confirm with CONTROL+SHIFT+ENTER. :) |
Vlookup...
Cool. It took me a while but I believe I basically understand it now.
Thanks for the very thorough explanation. -- Kevin Vaughn "Domenic" wrote: Let's assume we'd like to find the production hours for UserID 'E' in the following table... UserID1 UserID2 UserID3 Name ProdHrs A B C John 40 D E F Joe 35 X Y Z Jane 45 ....and that we have the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(E2:E4,MATCH(TRUE,MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0))0 ,0)) Here's how it breaks down... A2:C4="E" evaluates to: {FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FA LSE} --(A2:C4="E") evaluates to: {0,0,0;0,1,0;0,0,0} Note that the double negative coerces TRUE and FALSE to 1 and 0, respectively. COLUMN(A2:C4) evaluates to: {1,2,3} COLUMN(A2:C4)^0 evaluates to: {1,1,1} TRANSPOSE(COLUMN(A2:C4)^0) evaluates to: {1;1;1} MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0)) evaluates to: {0;1;0} MMULT(--(A2:C4="E"),TRANSPOSE(COLUMN(A2:C4)^0))0 evaluates to: {FALSE;TRUE;FALSE} If you're not familiar with matrix multiplication, have a look at the following link... http://www.purplemath.com/modules/mtrxmult.htm Now that you've seen how the formula breaks down, here's how the evaluation takes place... =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},TRANSPOSE({1,2,3}^ 0))0,0)) =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},TRANSPOSE({1,1,1}) )0,0)) =INDEX({40;35;45},MATCH(TRUE,MMULT({0,0,0;0,1,0;0, 0,0},{1;1;1})0,0)) =INDEX({40;35;45},MATCH(TRUE,{0;1;0}0,0)) =INDEX({40;35;45},MATCH(TRUE,{FALSE;TRUE;FALSE},0) ) =INDEX({40;35;45},2) ....which returns 35. Hope this helps! In article , Kevin Vaughn wrote: If you're willing to explain it, I'm willing to try to understand it. It does seem to work. -- Kevin Vaughn |
| All times are GMT +1. The time now is 07:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com