VLookup? Or something else
 

I have a workbook with two tabs(worksheets) on it. The first contains a
copied 6 column database from an external source. In the second worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division" in
column G, and "1" in column C and "521" in Column A. When you find that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there something
else I can use that would work? Match? Index? Or is there a VBA formula I
can use. I am a little familair with VBA.

Yes it is possible

=INDEX(D1:D500,MATCH(1,(G1:G500="East Division")*(C1:C500=1)*(A1:A500=521),0))

entered with ctrl + shift & enter

If you want to use a defined name it becomes a bit ltrickier
Assume the table is named MyTable and holds the range
A1:G500

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,7)="East
Division")*(INDEX(MyTable,,3)=1)*(INDEX(MyTable,,1 )=521),0),4)

also array entered


Regards,

Peo Sjoblom


"JICDB" wrote:

I have a workbook with two tabs(worksheets) on it. The first contains a
copied 6 column database from an external source. In the second worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division" in
column G, and "1" in column C and "521" in Column A. When you find that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there something
else I can use that would work? Match? Index? Or is there a VBA formula I
can use. I am a little familair with VBA.

I am going to try this first thing tomorrow (time to go home in Chicago). I
was playing around waiting for some help and found that if I concatenated the
fields to one and placed it it in the first column of my data and the first
column of my report, I could match concatinated fields. So far it has worked
but I would rather not have columns in my report and database that are
temporary holding cells. Thanks for you help.

"Peo Sjoblom" wrote:

Yes it is possible

=INDEX(D1:D500,MATCH(1,(G1:G500="East Division")*(C1:C500=1)*(A1:A500=521),0))

entered with ctrl + shift & enter

If you want to use a defined name it becomes a bit ltrickier
Assume the table is named MyTable and holds the range
A1:G500

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,7)="East
Division")*(INDEX(MyTable,,3)=1)*(INDEX(MyTable,,1 )=521),0),4)

also array entered


Regards,

Peo Sjoblom


"JICDB" wrote:

I have a workbook with two tabs(worksheets) on it. The first contains a
copied 6 column database from an external source. In the second worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division" in
column G, and "1" in column C and "521" in Column A. When you find that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there something
else I can use that would work? Match? Index? Or is there a VBA formula I
can use. I am a little familair with VBA.

I tried the Index function and for some reason I cannot get it to work. At
the end of your email you said to "entered with ctrl + Shift & enter". Maybe
this is what I'm doing wrong. How do you enter something with ctrl + Shift &
enter?

"JICDB" wrote:

I am going to try this first thing tomorrow (time to go home in Chicago). I
was playing around waiting for some help and found that if I concatenated the
fields to one and placed it it in the first column of my data and the first
column of my report, I could match concatinated fields. So far it has worked
but I would rather not have columns in my report and database that are
temporary holding cells. Thanks for you help.

"Peo Sjoblom" wrote:

Yes it is possible

=INDEX(D1:D500,MATCH(1,(G1:G500="East Division")*(C1:C500=1)*(A1:A500=521),0))

entered with ctrl + shift & enter

If you want to use a defined name it becomes a bit ltrickier
Assume the table is named MyTable and holds the range
A1:G500

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,7)="East
Division")*(INDEX(MyTable,,3)=1)*(INDEX(MyTable,,1 )=521),0),4)

also array entered


Regards,

Peo Sjoblom


"JICDB" wrote:

I have a workbook with two tabs(worksheets) on it. The first contains a
copied 6 column database from an external source. In the second worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division" in
column G, and "1" in column C and "521" in Column A. When you find that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there something
else I can use that would work? Match? Index? Or is there a VBA formula I
can use. I am a little familair with VBA.

Because I am using 2 different tabs - one with the data and the other with
the report - how do I incorporate the tab/worksheet name into this formula
and what is meant by
"entered with ctrl + shift & enter" in your post?

"Peo Sjoblom" wrote:

Yes it is possible

=INDEX(D1:D500,MATCH(1,(G1:G500="East Division")*(C1:C500=1)*(A1:A500=521),0))

entered with ctrl + shift & enter

If you want to use a defined name it becomes a bit ltrickier
Assume the table is named MyTable and holds the range
A1:G500

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,7)="East
Division")*(INDEX(MyTable,,3)=1)*(INDEX(MyTable,,1 )=521),0),4)

also array entered


Regards,

Peo Sjoblom


"JICDB" wrote:

I have a workbook with two tabs(worksheets) on it. The first contains a
copied 6 column database from an external source. In the second worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division" in
column G, and "1" in column C and "521" in Column A. When you find that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there something
else I can use that would work? Match? Index? Or is there a VBA formula I
can use. I am a little familair with VBA.

To incorporate different sheets select the sheet with the mouse when you
apply the formula and the sheet names will be entered automatically
instead of enter the formula with enter hold down ctrl + shift & enter at
the same time, it's an array formula

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JICDB" wrote in message
...
Because I am using 2 different tabs - one with the data and the other with
the report - how do I incorporate the tab/worksheet name into this formula
and what is meant by
"entered with ctrl + shift & enter" in your post?

"Peo Sjoblom" wrote:

Yes it is possible

=INDEX(D1:D500,MATCH(1,(G1:G500="East
Division")*(C1:C500=1)*(A1:A500=521),0))

entered with ctrl + shift & enter

If you want to use a defined name it becomes a bit ltrickier
Assume the table is named MyTable and holds the range
A1:G500

=INDEX(MyTable,MATCH(1,(INDEX(MyTable,,7)="East
Division")*(INDEX(MyTable,,3)=1)*(INDEX(MyTable,,1 )=521),0),4)

also array entered


Regards,

Peo Sjoblom


"JICDB" wrote:

I have a workbook with two tabs(worksheets) on it. The first contains
a
copied 6 column database from an external source. In the second
worksheet I
want to pull out a specific number from that database based on three
criteria. In English, what I want to say is:

In this Defined Range pinpoint the record that contains "East Division"
in
column G, and "1" in column C and "521" in Column A. When you find
that
record, show me what is in column D of that record.

Can you use lookup tables for more than one criteria? Is there
something
else I can use that would work? Match? Index? Or is there a VBA
formula I
can use. I am a little familair with VBA.