LOOKUP w/ multiple search columns?
 

I hope Im phrasing this right.


Heres my data table

======W X Y Z N
A B C D 1 2 3 4 5
E F G H 6 7 8 9 10


The letters are the search variables and the numbers are potential
outputs.

I want to be able to input, (A,B,C,D, W) and get an output of 1
(A,B,C,D,X)=2
(E,F,G, H,W)=6

how do i do this?


--
Fotop
------------------------------------------------------------------------
Fotop's Profile: http://www.excelforum.com/member.php...o&userid=27880
View this thread: http://www.excelforum.com/showthread...hreadid=525988

LOOKUP w/ multiple search columns?
 

I put the table in Sheet2 (A1:I10) and put the values to match up in sheet1:

A1:D1 for the first four columns (to determine the row)
and E1 to determine the column:

=INDEX(Sheet2!$A$1:$I$10,
MATCH(1,((A1=Sheet2!$A$1:$A$10)
*(B1=Sheet2!$B$1:$B$10)
*(C1=Sheet2!$C$1:$C$10)
*(D1=Sheet2!$D$1:$D$10)),0),
MATCH(E1,Sheet2!A1:I1,0))

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

Adjust the range to match--but you can't use the whole column.

Fotop wrote:

I hope Im phrasing this right.

Heres my data table

======W X Y Z N
A B C D 1 2 3 4 5
E F G H 6 7 8 9 10

The letters are the search variables and the numbers are potential
outputs.

I want to be able to input, (A,B,C,D, W) and get an output of 1
(A,B,C,D,X)=2
(E,F,G, H,W)=6

how do i do this?

--
Fotop
------------------------------------------------------------------------
Fotop's Profile: http://www.excelforum.com/member.php...o&userid=27880
View this thread: http://www.excelforum.com/showthread...hreadid=525988

--

Dave Peterson