Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Wizard
I am using the lookup wizard for a table shown below. The lookup wizard gives
access for only 2 parameters, column A and any other column in the table. Does the wizard can give access for 4 parameters? Thanks A B C D E 1 No Col ID Index 2 01/01/09 1 blue 123 abc 3 08/01/09 2 red 456 def 4 24/02/09 3 yellow 789 ghi 5 31/03/09 6 green 654 jkl 6 05/05/09 5 pink 258 mno 7 07/07/09 23 black 369 pqr 8 22/10/09 4 white 741 stu |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Wizard
I am not sure I understood your query. Are you looking at a formula to check
whether a record in the range exists with more than 1 lookup_value (exact match to each cell in the same row). Could you please elaborate and post what you are expecting as the formula result If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: I am using the lookup wizard for a table shown below. The lookup wizard gives access for only 2 parameters, column A and any other column in the table. Does the wizard can give access for 4 parameters? Thanks A B C D E 1 No Col ID Index 2 01/01/09 1 blue 123 abc 3 08/01/09 2 red 456 def 4 24/02/09 3 yellow 789 ghi 5 31/03/09 6 green 654 jkl 6 05/05/09 5 pink 258 mno 7 07/07/09 23 black 369 pqr 8 22/10/09 4 white 741 stu |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Wizard
Hi,
I'm not sure I understand but here's a formula that looks up columns A, B, C & D and returns column E =INDEX(E2:E8,MATCH(1,(A2:A8=F2)*(B2:B8=F3)*(C2:C8= F4)*(D2:D8=F5),0)) The lookup values go in f2, f3, f4 & f5 and it is an *ARRAY* formula This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "MAX" wrote: I am using the lookup wizard for a table shown below. The lookup wizard gives access for only 2 parameters, column A and any other column in the table. Does the wizard can give access for 4 parameters? Thanks A B C D E 1 No Col ID Index 2 01/01/09 1 blue 123 abc 3 08/01/09 2 red 456 def 4 24/02/09 3 yellow 789 ghi 5 31/03/09 6 green 654 jkl 6 05/05/09 5 pink 258 mno 7 07/07/09 23 black 369 pqr 8 22/10/09 4 white 741 stu |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Wizard
"Jacob Skaria" wrote: I am not sure I understood your query. Are you looking at a formula to check whether a record in the range exists with more than 1 lookup_value (exact match to each cell in the same row). Could you please elaborate and post what you are expecting as the formula result If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: I am using the lookup wizard for a table shown below. The lookup wizard gives access for only 2 parameters, column A and any other column in the table. Does the wizard can give access for 4 parameters? Thanks A B C D E 1 No Col ID Index 2 01/01/09 1 blue 123 abc 3 08/01/09 2 red 456 def 4 24/02/09 3 yellow 789 ghi 5 31/03/09 6 green 654 jkl 6 05/05/09 5 pink 258 mno 7 07/07/09 23 black 369 pqr 8 22/10/09 4 white 741 stu Sorry I forgot to tell you that I'm a beginner on lookup. All I want is that when I choose for example the date 07/07/09 in the lookup wizard, I will have the answer: 23 - black - 369 and the lookup value is pqr. Thanks a lot. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Wizard
--If you want to lookup "pqr" and extract all other fields try the below
formula and copy the formula to the right... (This can be done only if 'pqr' appears only once in the list) =INDEX(A1:A10,MATCH("pqr",$E$1:$E$10,0)) --If you have more lookup values like the date and "pqr" then try the below formula with the date 7/7/2009 in a cell say (F1) F1 = 7/7/2009 In G1 enter the below formula and copy to H1 and I1 ...will return the 3 fields =INDEX(B2:B10,MATCH(1,($A$2:$A$10=$E$19)*($E$2:$E$ 10="pqr"),0)) Please note that this formula is to be array entered (/Ctrl+Shift+Enter) If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: "Jacob Skaria" wrote: I am not sure I understood your query. Are you looking at a formula to check whether a record in the range exists with more than 1 lookup_value (exact match to each cell in the same row). Could you please elaborate and post what you are expecting as the formula result If this post helps click Yes --------------- Jacob Skaria "MAX" wrote: I am using the lookup wizard for a table shown below. The lookup wizard gives access for only 2 parameters, column A and any other column in the table. Does the wizard can give access for 4 parameters? Thanks A B C D E 1 No Col ID Index 2 01/01/09 1 blue 123 abc 3 08/01/09 2 red 456 def 4 24/02/09 3 yellow 789 ghi 5 31/03/09 6 green 654 jkl 6 05/05/09 5 pink 258 mno 7 07/07/09 23 black 369 pqr 8 22/10/09 4 white 741 stu Sorry I forgot to tell you that I'm a beginner on lookup. All I want is that when I choose for example the date 07/07/09 in the lookup wizard, I will have the answer: 23 - black - 369 and the lookup value is pqr. Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wizard Template | Excel Discussion (Misc queries) | |||
Is there a formula wizard? | Excel Discussion (Misc queries) | |||
Allow the use of the fx wizard within the fx wizard for nesting | Excel Worksheet Functions | |||
lookup wizard | Excel Discussion (Misc queries) | |||
Wizard | Excel Discussion (Misc queries) |