VLOOKUP with 2 Criterias

[Roni]

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

 

[CLR]

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,4,FALSE)

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Not VLOOKUP, but a combination of INDEX and MATCH

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

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

 

[CLR]

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

My apologies,
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

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.)

 

[Bob Phillips]

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

 

[Roni]

Guys,

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

Roni

 

[fLiPMoD£]

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

Thanks in advance

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

 

[Peo Sjoblom]

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;FALSE;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;FALSE;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

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

Thanks in advance

...Coming From where I'm From.

 

[Bob Phillips]

.... what did I say?

Bob

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

Thanks in advance

...Coming From where I'm From.

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

 

[Dave Peterson]

Moe: Lady, you must be psychic!

... what did I say?

Bob

 

[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.

 

[Bob Phillips]

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

 

[Domenic]

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.

 

[RagDyeR]

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

 

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

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

Maybe chr(1)???

 

[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.)

 

[Domenic]

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))

 

[Aladin Akyurek]

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 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,LOOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6),"")

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