Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Guys,
I prefer to use the index and match function, although concatenate is also working =). Thanks for the advise. Roni |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
.... 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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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 |
#17
|
|||
|
|||
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 |
#18
|
|||
|
|||
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 |
#19
|
|||
|
|||
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 |
#20
|
|||
|
|||
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),"") |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
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 |
#23
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |