search for value based on conditions of other cells in the same row?

[dan-x]

I've been searching to see if I can figure out how to write an Excel
formula that looks at another workbook and searches a table for a
cell's value based on conditions of other cells in the same row.
I'm afraid what I want might be too complicated though.

Here's what I want to do as an example using the table below. Can I
write a formula that searches another workbook table and if the QUEUE
column contains "Appeal" and the SUBQUEUE column contains "No
Subqueue" and the DSTR_NR column contains "1604" then it knows to
go on to the next column, REGS, and select its value ("1") without
specifying the row number of the REGS cell? (Make sense?)

QUEUE SUBQUEUE DSTR_NR REGS
Appeal No Subqueue 1603 8
Appeal No Subqueue 1604 1
Info Control CVP 1605 4
Info Control CVP 1606 8

Thanks,
Danny
!!!

 

[RagDyer]

Say your datalist was in a WB named "8-24-06",
And the list was in the range A1 to D10, with columns configured the same as
your example.

Try this *array* formula in the "other" WB:

=INDEX('[8-24-06.xls]Sheet1'!$D$1:$D$10,MATCH(1,('[8-24-06.xls]Sheet1'!$A$1:$A$10="Appeal")*('[8-24-06.xls]Sheet1'!$B$1:$B$10="No
Subqueue")*('[8-24-06.xls]Sheet1'!$C$1:$C$10=1604),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

When you close the "8-24-06" WB, this formula will expand (grow) to include
the full path to the drive and folders where it resides.
Depending on where you have it saved, this formula might turn out to be
exceedingly large.

 

[dan-x]

RagDyer...

Thanks!!! That worked superbly!!! I never would have come up with
that on my own.

Now I have another question but it's not as important as my original
inquiry. When there is no data that meets the formula's conditions I
get an #N/A error in the formula's cell. Is there something that I can
add to the formula so that it will just appear blank or, if not, at
least display a zero instead?

Thanks so much for your technical wizardry.

-Danny

 

[RagDyer]

I was afraid you were going to ask that.

This error trap will really make the formula a monster ! ! !

=IF(ISNA(MATCH("Appeal"&"No
Subqueue"&1604,'[8-24-06.xls]Sheet1'!$A$1:$A$10&'[8-24-06.xls]Sheet1'!$B$1:$B$10&'[8-24-06.xls]Sheet1'!$C$1:$C$10,0)),"",INDEX('[8-24-06.xls]Sheet1'!$D$1:$D$10,MATCH(1,('[8-24-06.xls]Sheet1'!$A$1:$A$10="Appeal")*('[8-24-06.xls]Sheet1'!$B$1:$B$10="No
Subqueue")*('[8-24-06.xls]Sheet1'!$C$1:$C$10=1604),0)))

Once again - Array formulas must be entered with CSE, <Ctrl> <Shift >
<Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

This will leave the cell blank if no match is found.

If you wish, you could add a text message such as "Not Found" or "No Value".

Look for the double quotes ( "" ), and *insert* whatever you wish in between
them.

However, if you're looking to calculate this cell, you might want a zero.
In that case *replace* the ( "" ) with a zero ( 0 ) so that the cell remains
numeric.

 

[RagDyer]

It just occurred to me that I used absolute references in the formula.
That's 28 extra characters ( $ ) that can be eliminated if you're *not*
going to copy this formula into a range.

=IF(ISNA(MATCH("Appeal"&"No
Subqueue"&1604,'[8-24-06.xls]Sheet1'!A1:A10&'[8-24-06.xls]Sheet1'!B1:B10&'[8-24-06.xls]Sheet1'!C1:C10,0)),"",INDEX('[8-24-06.xls]Sheet1'!D110,MATCH(1,('[8-24-06.xls]Sheet1'!A1:A10="Appeal")*('[8-24-06.xls]Sheet1'!B1:B10="No
Subqueue")*('[8-24-06.xls]Sheet1'!C1:C10=1604),0)))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I was afraid you were going to ask that.

This error trap will really make the formula a monster ! ! !

=IF(ISNA(MATCH("Appeal"&"No
Subqueue"&1604,'[8-24-06.xls]Sheet1'!$A$1:$A$10&'[8-24-06.xls]Sheet1'!$B$1:$B$10&'[8-24-06.xls]Sheet1'!$C$1:$C$10,0)),"",INDEX('[8-24-06.xls]Sheet1'!$D$1:$D$10,MATCH(1,('[8-24-06.xls]Sheet1'!$A$1:$A$10="Appeal")*('[8-24-06.xls]Sheet1'!$B$1:$B$10="No
Subqueue")*('[8-24-06.xls]Sheet1'!$C$1:$C$10=1604),0)))

Once again - Array formulas must be entered with CSE, <Ctrl> <Shift >
<Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

This will leave the cell blank if no match is found.

If you wish, you could add a text message such as "Not Found" or "No
Value".

Look for the double quotes ( "" ), and *insert* whatever you wish in
between them.

However, if you're looking to calculate this cell, you might want a zero.
In that case *replace* the ( "" ) with a zero ( 0 ) so that the cell
remains numeric.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

RagDyer...

Thanks!!! That worked superbly!!! I never would have come up with
that on my own.

Now I have another question but it's not as important as my original
inquiry. When there is no data that meets the formula's conditions I
get an #N/A error in the formula's cell. Is there something that I can
add to the formula so that it will just appear blank or, if not, at
least display a zero instead?

Thanks so much for your technical wizardry.

-Danny

 

[Dan-X !]

Thanks so much! I was able to construct a formula that worked from
your example. It is long but that's ok.

Very appreciated!!!

Thanks again,
-Danny

 

[RagDyer]

You're very welcome, and appreciate the feed-back.