VLOOKUP with 2 Criterias
 

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

Hi Roni......

What I would do is to insert a helper column in Sheet 2 to the left of the
Serial# column, called CONCAT(in A1), then in A2 I would put this formula
and copy down........
=CONCATENATE(B2,C2)

Then in Location cell C2 on Sheet1 put this formula and copy
down............
=VLOOKUP(CONCATENATE(B2,C2),SHEET2!,A:D,4,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Roni" wrote in message
ups.com...
Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

Not VLOOKUP, but a combination of INDEX and MATCH

=INDEX(Sheet2!C1:C3,MATCH(A1&B1,Sheet2!A1:A3&Sheet 2!B1:B3,0))

This is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Roni" wrote in message
ups.com...
Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

Sorry, second formula should have been........
=VLOOKUP(CONCATENATE(A2,B2),SHEET2!A:D,4,FALSE)

My apologies,
Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
Hi Roni......

What I would do is to insert a helper column in Sheet 2 to the left of the
Serial# column, called CONCAT(in A1), then in A2 I would put this formula
and copy down........
=CONCATENATE(B2,C2)

Then in Location cell C2 on Sheet1 put this formula and copy
down............
=VLOOKUP(CONCATENATE(B2,C2),SHEET2!,A:D,4,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Roni" wrote in message
ups.com...
Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson

That's a great formula Dave, but somewhat obtuse ;-)

Big problem with this, is that every posting will require a follow-up
explaining how it works (I'm writing it as we speak ;-))

Bob


"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

....Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson

assuming you mean the match 1 part,

a2=othersheet!$a$1:$a$10

the above will return an array of Boolean values TRUE or FALSE, an example
could look like this

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FAL SE;FALSE}

the next array will do the same

b2=othersheet!$b$1:$b$10

now when you calculate TRUE or FALSE they will return 1 for TRUE of 0 for
FALSE
only TRUE*TRUE will return one, all other options will return FALSE
so if the second looks like


{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FA LSE;FALSE}

and when you multiply them they will return an array like


{0;0;0;0;0;0;0;1;0;0}

thus

=MATCH(1,{0;0;0;0;0;0;0;1;0;0},0)

will return 8 (the 8th value is 1)

then using index it will return the 8th row in the index range



--
Regards,

Peo Sjoblom


"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson

.... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson

Moe: Lady, you must be psychic!



Bob Phillips wrote:

... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson



--

Dave Peterson

concatenate will work nicely until you get a table like:

AAA BBB 1
AA ABBB 2
A AABBB 3
AAABBB 4

And you want to return the 3.


Roni wrote:

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

--

Dave Peterson

I wish you wouldn't use these American cultural references, I never
understand them :-)

"Dave Peterson" wrote in message
...
Moe: Lady, you must be psychic!



Bob Phillips wrote:

... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the
data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson



--

Dave Peterson

But, as you already know, you can get around this by modifying the
formula as follows...

=INDEX(C1:C4,MATCH(D1&","&E1,A1:A4&","&B1:B4,0))

....confirmed with CONTROL+SHIFT+ENTER, and where D1 contains your first
criterion, 'A', and E1 contains your second, 'AABBB.

In article ,
Dave Peterson wrote:

concatenate will work nicely until you get a table like:

AAA BBB 1
AA ABBB 2
A AABBB 3
AAABBB 4

And you want to return the 3.


Roni wrote:

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

I don't understand them either, and I think I'm American!?!?<g
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Bob Phillips" wrote in message
...
I wish you wouldn't use these American cultural references, I never
understand them :-)

"Dave Peterson" wrote in message
...
Moe: Lady, you must be psychic!



Bob Phillips wrote:

... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the
data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson



--

Dave Peterson

If one's data required, a hyphen or other separator could be used..........

=VLOOKUP(CONCATENATE(A2&"-"&B2),SHEET2!A:D,4,FALSE)

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote in message
...
concatenate will work nicely until you get a table like:

AAA BBB 1
AA ABBB 2
A AABBB 3
AAABBB 4

And you want to return the 3.


Roni wrote:

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

--

Dave Peterson

I think I'd use a character that was less likely to show up in the cells.

Maybe chr(1)???



Domenic wrote:

But, as you already know, you can get around this by modifying the
formula as follows...

=INDEX(C1:C4,MATCH(D1&","&E1,A1:A4&","&B1:B4,0))

...confirmed with CONTROL+SHIFT+ENTER, and where D1 contains your first
criterion, 'A', and E1 contains your second, 'AABBB.

In article ,
Dave Peterson wrote:

concatenate will work nicely until you get a table like:

AAA BBB 1
AA ABBB 2
A AABBB 3
AAABBB 4

And you want to return the 3.


Roni wrote:

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

--

Dave Peterson

It's a reference to a Three Stooges scene.

http://www.auelfans.ca/discus/messages/12/1844.html

(Weird link for this, but...)

That one was remade several times, once as "Hoi Polloi." Also the Dance Lesson
("Watch closely, gentlemen, and do exactly as I do") and the pie stuck to the
ceiling (MATRON: "Why, you act as if the Sword of Damocles were hanging over
your head." MOE: "Lady, you must be psychic!" Runs off, leaving her to look
straight up just in time to have the pie let go and smash into her face.)





RagDyeR wrote:

I don't understand them either, and I think I'm American!?!?<g
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Bob Phillips" wrote in message
...
I wish you wouldn't use these American cultural references, I never
understand them :-)

"Dave Peterson" wrote in message
...
Moe: Lady, you must be psychic!



Bob Phillips wrote:

... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the
data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson



--

Dave Peterson

--

Dave Peterson

Yes, definitely. And I see all sorts of variations, including the use
of Char(27), "@", etc.

But I'm with you Dave, I prefer this syntax instead...

=index(...,match(1,(...)*(...),0))

In article ,
Dave Peterson wrote:

I think I'd use a character that was less likely to show up in the cells.

Maybe chr(1)???



Domenic wrote:

But, as you already know, you can get around this by modifying the
formula as follows...

=INDEX(C1:C4,MATCH(D1&","&E1,A1:A4&","&B1:B4,0))

...confirmed with CONTROL+SHIFT+ENTER, and where D1 contains your first
criterion, 'A', and E1 contains your second, 'AABBB.

In article ,
Dave Peterson wrote:

concatenate will work nicely until you get a table like:

AAA BBB 1
AA ABBB 2
A AABBB 3
AAABBB 4

And you want to return the 3.


Roni wrote:

Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni

Roni wrote:
Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni


Why? If you have a lot of retrieving to do or your spreadsheet is
infested with more array formulas, formulas with volatile functions,
etc., re-consider your preferences in terms of efficiency. Creating an
additional column by means of concatenation CLR suggested can be made
more robust with an improbable char like "#", "@", "," or a
non-printable CHAR(1)...

Let column B on Sheet2 house Serial # and C Asset Code, and D Location...

In A2 enter & copy down:

=B2&"#"&C2

Intermezzo. If you are on Excel 2003, convert the area A:D into a list
with Data|List|Create List. This list option will automatically copy the
concatenation formula down for every new record you might add.

[1] Invoke a VLOOKUP formula on Sheet1 with the match-type set to 0
(FALSE)...

=VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6,4,0)

[2] Sort the area A:E on Sheet2 in ascending order on Serial # then
Asset Code (With List, on the concatenation column) and invoke a fast
working LOOKUP formula or VLOOKUP formula with the match-type set to 1
(TRUE)...

=IF(LOOKUP(A2&"#"&B2,Sheet2!$A$2:$A$6)=A2&"#"&B2,L OOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6),"")

=IF(VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$A$6,1)=A2&"#"&B 2,VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6,4,1),"")

I think it was a similar reply the last time I asked :-)

Bob

"Dave Peterson" wrote in message
...
It's a reference to a Three Stooges scene.

http://www.auelfans.ca/discus/messages/12/1844.html

(Weird link for this, but...)

That one was remade several times, once as "Hoi Polloi." Also the Dance
Lesson
("Watch closely, gentlemen, and do exactly as I do") and the pie stuck to
the
ceiling (MATRON: "Why, you act as if the Sword of Damocles were hanging
over
your head." MOE: "Lady, you must be psychic!" Runs off, leaving her to
look
straight up just in time to have the pie let go and smash into her face.)





RagDyeR wrote:

I don't understand them either, and I think I'm American!?!?<g
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Bob Phillips" wrote in message
...
I wish you wouldn't use these American cultural references, I never
understand them :-)

"Dave Peterson" wrote in message
...
Moe: Lady, you must be psychic!



Bob Phillips wrote:

... what did I say?

Bob

"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very
interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,

match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of
enter.
If
you
do it
correctly, excel will wrap curly brackets {} around your
formula.
(don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the
data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location"
if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson



--

Dave Peterson

--

Dave Peterson

Aladin,

That's true, the array formula takes a lot of time to process big excel
file. Are there any ways to fasten the processing time for array
formula?

Thanks,
Roni

Roni wrote:
Aladin,

That's true, the array formula takes a lot of time to process big excel
file. Are there any ways to fasten the processing time for array
formula?

Thanks,
Roni


Yes, if you can sort the data, calculate the subranges (in additional
columns, and apply the array formula to calculated subranges.

Why not try the proposal for concatenating, sorting, and invoking a
LOOKUP formula for it will certainly beat the foregoing in performance?