Vlookup...

[Guest]

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

 

[Guest]

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(VLOOKUP(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

 

[Biff]

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

 

[Guest]

Helo Biff.

Because the names are almost always spelled and formatted idfferently, or I
would have already followed this path.

Mark

 

[Domenic]

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!

 

[Gilbert De Ceulaer]

This formula can be made shorter and more readable with the SETV-GETV of the
MOREFUNC-addon from (e-mail address removed).
=IF(ISERROR( setv(VLOOKUP(B1,hours!A1:E5,5,FALSE)),1)=FALSE,getv(1),
IF(ISERROR( setv(VLOOKUP(B1,hours!B1:E5,4,FALSE)),2)=FALSE,getv(2),
IF(ISERROR( setv(VLOOKUP(B1,hours!C1:E5,3,FALSE)),3)=FALSE,getv(3),"no
record")))
GDC

 

[Guest]

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

 

[Guest]

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

 

[Gilbert De Ceulaer]

The formula with the SETV-GETV of the MOREFUNC-add1n from (e-mail address removed)
would be
=IF(ISERROR( setv(VLOOKUP(B1,hours!A15,4,FALSE)),1)=FALSE,getv(1),
IF(ISERROR( setv(VLOOKUP(B1,hours!B15,3,FALSE)),2)=FALSE,getv(2),
IF(ISERROR( setv(VLOOKUP(B1,hours!C15,2,FALSE)),3)=FALSE,getv(3),"no
record")))
GDC

 

[Guest]

Hello Gilbert. Thank you, but we can't add outside add-ons to our program.

Mark

 

[Gilbert De Ceulaer]

Sorry, mistake
it should be :
=IF(ISERROR(
SETV(VLOOKUP(B1;hours!A15;4;FALSE);1 ))=FALSE;GETV(1);
IF(ISERROR(
SETV(VLOOKUP(B1;hours!B15;3;FALSE);2 ))=FALSE;GETV(2);
IF(ISERROR(
SETV(VLOOKUP(B1;hours!C15;2;FALSE);3 ))=FALSE;GETV(3);"no
record")))

 

[Gilbert De Ceulaer]

OK, in that case :
=IF(ISERROR( VLOOKUP(A7;hours1!A15;4;FALSE))=FALSE;
VLOOKUP(A7;hours1!A15;4;FALSE);
IF(ISERROR( VLOOKUP(A7;hours1!B15;3;FALSE))=FALSE;
VLOOKUP(A7;hours1!B15;3;FALSE);
IF(ISERROR( VLOOKUP(A7;hours1!C15;2;FALSE))=FALSE;
VLOOKUP(A7;hours1!C15;2;FALSE);"no record")))

This OK ?
GDC

 

[Guest]

Thank you Gilbert - solution worked.

Mark

 

[Guest]

Thank you Elkar.

After further review of your solution, it does indeed work.

Mark

 

[Guest]

If you're willing to explain it, I'm willing to try to understand it. It
does seem to work.

 

[Biff]

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]

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,FALSE}

--(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!

 

[Domenic]

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.

 

[Guest]

Cool. It took me a while but I believe I basically understand it now.
Thanks for the very thorough explanation.