Lookup Wizard

[MAX]

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

 

[Jacob Skaria]

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

 

[Mike H]

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)*(D28=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]

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

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.

 

[Jacob Skaria]

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