Hi

I have data in four columns. Column A have numbers in the cells (numbers between 1-20), column B with numbers 1-5, colum C with numbers 1-6. These numbers are not in any kind of order and sometimes repeat themselves (shown more than once). In colum D I have text in each cell. What I need to do is to extract the line of text that corresponds to a row that meets a number criteria.

For example:
I get numbers 16, 4, and 2 (colums A. B and C). I need to know what the text is in column D that has these three numbers in its row. I have tried:

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

but that just wants to add the cell value in coilumn D and return a value of zero

please can anyone help

Cheers in advance

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

To retrieve from d1:d20, use the index / match "equivalent":
=index(d1:d20,match(1,(a1:a20=16)*(b1:b20=4)*(c1:c 20=2),0))
Array-enter the formula above by pressing CTRL+SHIFT+ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"shnim1" wrote in message
...

Hi

I have data in four columns. Column A have numbers in the cells
(numbers between 1-20), column B with numbers 1-5, colum C with numbers
1-6. These numbers are not in any kind of order and sometimes repeat
themselves (shown more than once). In colum D I have text in each cell.
What I need to do is to extract the line of text that corresponds to a
row that meets a number criteria.

For example:
I get numbers 16, 4, and 2 (colums A. B and C). I need to know what the
text is in column D that has these three numbers in its row. I have
tried:

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

but that just wants to add the cell value in coilumn D and return a
value of zero

please can anyone help

Cheers in advance




--
shnim1

Thabnks...it worked like a charm!

Good to hear that. You're welcome.

Do try out the alternatives from the other responders as well ..
It's always good to know the options available.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 2:41 am, shnim1 wrote:
Thanks...it worked like a charm!

With
A1:A100 contians numbers between 1 and 16
B1:B100 contains numbers between 1 and 5
C1:C100 contains numbers between 1 and 6
D1:D1000 contains text

Try this:
F1: 16
G1: 4
H1: 2

I1: =LOOKUP(F1&G1&H1,A1:A100&B1:B100&C1:C100,D1:D100)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"shnim1" wrote:


Hi

I have data in four columns. Column A have numbers in the cells
(numbers between 1-20), column B with numbers 1-5, colum C with numbers
1-6. These numbers are not in any kind of order and sometimes repeat
themselves (shown more than once). In colum D I have text in each cell.
What I need to do is to extract the line of text that corresponds to a
row that meets a number criteria.

For example:
I get numbers 16, 4, and 2 (colums A. B and C). I need to know what the
text is in column D that has these three numbers in its row. I have
tried:

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

but that just wants to add the cell value in coilumn D and return a
value of zero

please can anyone help

Cheers in advance




--
shnim1

Oops! That only works if the column A through C data is sorted ascending!

This one actually works:
(Using the same rules I posted before)

I1: =INDEX(D1:D100,MATCH(F1&G1&H1,INDEX(A1:A100&B1:B10 0&C1:C100,0),0))

or...the array formula version (commited with ctrl+****+enter)
=INDEX(D1:D100,MATCH(F1&G1&H1,A1:A100&B1:B100&C1:C 100,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

With
A1:A100 contians numbers between 1 and 16
B1:B100 contains numbers between 1 and 5
C1:C100 contains numbers between 1 and 6
D1:D1000 contains text

Try this:
F1: 16
G1: 4
H1: 2

I1: =LOOKUP(F1&G1&H1,A1:A100&B1:B100&C1:C100,D1:D100)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"shnim1" wrote:


Hi

I have data in four columns. Column A have numbers in the cells
(numbers between 1-20), column B with numbers 1-5, colum C with numbers
1-6. These numbers are not in any kind of order and sometimes repeat
themselves (shown more than once). In colum D I have text in each cell.
What I need to do is to extract the line of text that corresponds to a
row that meets a number criteria.

For example:
I get numbers 16, 4, and 2 (colums A. B and C). I need to know what the
text is in column D that has these three numbers in its row. I have
tried:

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

but that just wants to add the cell value in coilumn D and return a
value of zero

please can anyone help

Cheers in advance




--
shnim1

=LOOKUP(10^10,SEARCH(16&4&2,A1:A20&B1:B20&C1:C20), D1:D20)


"shnim1" wrote:


Hi

I have data in four columns. Column A have numbers in the cells
(numbers between 1-20), column B with numbers 1-5, colum C with numbers
1-6. These numbers are not in any kind of order and sometimes repeat
themselves (shown more than once). In colum D I have text in each cell.
What I need to do is to extract the line of text that corresponds to a
row that meets a number criteria.

For example:
I get numbers 16, 4, and 2 (colums A. B and C). I need to know what the
text is in column D that has these three numbers in its row. I have
tried:

=sumproduct(--(a1:a20=16),--(b1:b20=4),--(c1:c20=2),(d1:d20))

but that just wants to add the cell value in coilumn D and return a
value of zero

please can anyone help

Cheers in advance




--
shnim1