LOOKUP(?) Challenge

[Joe]

In Excel 2002, I have a problem that I can’t seem to solve. I gave this to
our company Excel Super User who usually solves things like this in a matter
of minutes, and she couldn’t get it to work either. I’m hoping some brilliant
mind here can help out.

I’ll make this as simple as I can. In the actual worksheets, there are many
more rows and columns, but for this example, I’ll reduce it just to get the
point across. Column and Row data are simply examples.

In Worksheet #1 (DATA):

Column: A N O P
Row 4: 650 132
Row 5: 655 235
Row 6: 623
Row 7: 642 358


Worksheet #2 (RESULTS):

What I’m trying to do is: Type in the same number from Column A from DATA in
RESULTS and then in RESULTS, (say in Column D, Row 20 for example ), have the
figure from DATA, N,O or P from the same row as the input value, show up.

The problem is that I don’t know what column the number will be in, in
advance, or if there will be any number at all. There will only be one number
in one of the columns, in other words, for 650, there will not be an “N†or
“P†entry, if a number is placed in “Oâ€.

From the example above, in RESULTS, if I type in 642, then I need to have
the value 358 appear in Column D, Row 20. If I type in 655, then the value
235 should appear. If I type in 623, the cell should remain blank.

So what formula can I put in the RESULTS worksheet, Column D, Row 20 that
will give me the value I’m looking for?

I’ve tried LOOKUP’s, IF statements, and a bunch of other things, but I just
can’t get it to work correctly. (The last time I looked, our Super User was
pulling her hair out while simultaneously banging her head on the desk.)

Any help would be really appreciated.

-Joe-

 

[bj]

the significant factor is that there will be only one field with data in the
lookup columns

try something like
=if(or(iserror(match(d19,data!A1:A50,0)),sum(offset(data!A1,
match(d19,data!A1:A50,0),14,1,3)=0)),"",sum(offset(data!A1,
match(d19,data!A1:A50,0),14,1,3)))

You will of course have to change the offset references for the size of your
table

 

[Mike Punko]

Your best bet would be to create another column with the value N44 then
use the VLOOKUP function.

Column: A N O P Q

Row 4: 650 132 132
Row 5: 655 235 235
Row 6: 623 0
Row 7: 642 358 358

Then in your RUSULTS worksheet you would use this code in cell B4.
=VLOOKUP(A4,'DATA'!A1:Q10,17,FALSE)

A B
Row 4: 655 235

Hope this helps you

 

[Domenic]

Assuming that Column A contains unique values, and that there will only
be one corresponding number in Column N, O, and P, try the following...

=SUMPRODUCT((DATA!A4:A7=C20)*(DATA!N47))

....where C20 contains your criterion, such 642.

Hope this helps!

 

[Joe]

Thanks a million guys! Unfortunately, I got sidetracked here at work and now
I'm heading out the door. I'll try these first thing tomorrow morning and
post back to let you know how I made out.

I do appreciate the assistance!

-Joe-

 

[Harlan Grove]

bj wrote...

the significant factor is that there will be only one field with data in the
lookup columns

try something like
=if(or(iserror(match(d19,data!A1:A50,0)),sum(offset(data!A1,
match(d19,data!A1:A50,0),14,1,3)=0)),"",sum(offset(data!A1,
match(d19,data!A1:A50,0),14,1,3)))

....

Bugs galore!

OFFSET(data!A1,.,14,1,3) refers to the 14th column to the right of
column A, which is column O, not column N.

Why the OR test? If the MATCH call didn't return an error, when would
the SUM call return an error if there weren't errors in the data range?
Shouldn't those errors be propagated?

sum(offset(data!A1,match(d19,data!A1:A50,0),14,1,3)=0) will *ALWAYS*
return 0 (FALSE) since SUM only adds numbers and ignores both TRUE and
FALSE values (as well as text).

This can be done with two function calls without error trapping,

=SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0))

and four function calls with minimal error trapping,

=IF(COUNTIF(data!A1:A50,D19),SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0)),"")

 

[Joe]

Well guys, again, Thanks VERY Much for your help! All 3 solutions worked just
fine. I will probably use Domenic's solution because it involves the least
amount of typing.

When I tried Mike's solution, I realized that I could apply that to another
area of the sheets and that just made things a whole lot easier!

I can't wait to show these to our Excel Super User when she comes in.

Again, Thank You for your help!

-Joe-

 

[bj]

bj wrote...
....

Bugs galore!

OFFSET(data!A1,.,14,1,3) refers to the 14th column to the right of
column A, which is column O, not column N.

Why the OR test? If the MATCH call didn't return an error, when would
the SUM call return an error if there weren't errors in the data range?
Shouldn't those errors be propagated?

sum(offset(data!A1,match(d19,data!A1:A50,0),14,1,3)=0) will *ALWAYS*
return 0 (FALSE) since SUM only adds numbers and ignores both TRUE and
FALSE values (as well as text).

This can be done with two function calls without error trapping,

=SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0))

and four function calls with minimal error trapping,

=IF(COUNTIF(data!A1:A50,D19),SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0)),"")

Mia culpa on the "14" I had started with a different function.
I am not sure about the rest of the bugs.
There was a request for a blank to be returned when there was no values in
N,O,P
Both of your equations would return a 0 if nothing was in N,O,P.

I like the simplicity of your second equation, but would still use the or()

=IF(or(COUNTIF(data!A1:A50,D19),SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0))=0),SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0)),"")

 

[Harlan Grove]

bj wrote...
....

There was a request for a blank to be returned when there was no values in
N,O,P
Both of your equations would return a 0 if nothing was in N,O,P.

Fair point.

I like the simplicity of your second equation, but would still use the or()

=IF(or(COUNTIF(data!A1:A50,D19),SUMPRODUCT(VLOOKUP(D19,data!A150,
{14;15;16},0))=0),SUMPRODUCT(VLOOKUP(D19,data!A150,{14;15;16},0)),"")

The OP did *NOT* state that he wanted all zero values to display as "".
That is, the OP didn't say his col N-P data couldn't contain zeros.
Your
unnecessary second argument to OR would cause numeric zeros in those
columns to return "".

If the goal is to return "" both when the value in D19 doesn't appear
in
data!A:A and when there is a match but columns N-P are nonnumeric, then
use the following array formula.

=IF(COUNT(VLOOKUP(A7,A14,{14;15;16},0)),
SUM(VLOOKUP(A7,A14,{14;15;16},0)),"")