Lookup using multiple sheets and multiple criteria, sorry if 2 pos
 

I need to look up and populate sheet one with matching criteria on sheet 2. I
need two separate formulas to do the following:
Formula 1:
If Column B on €œData€� Sheet is Greater than Cell C3 on Summary Sheet AND
Column C on €œData€� Sheet indicates €œTRUE€�, I need the €œName€� (from column A)
and €œPercent€� (from column B) on €œData€� Sheet to populate in the €œName€�
(column B) and €œPercent€� (column C) on the €œsummary Sheet€�. For example,
according to the data below, only Jen, Rob, Tony, John, and Jules, along with
their corresponding percentage will show up in columns B and C on the
€œSummary€� Sheet.

Formula 2:
If Column F on the €œData€� sheet is equal to or greater than 1000, AND
Column €œG€� on the €œData€� Sheet is less than Cell F3 on the €œSummary€� Sheet, I
need the I need the €œName€� (from column A) and €œPercent€� (from column F) on
€œData€� Sheet to populate in the €œName€� (column E) and €œPercent€� (column F) on
the €œSummary€� Sheet. For example, according to the data below only Tony,
John, and Jules along with their corresponding percentage will show up in
columns E and F on the €œSummary€� Sheet.

As always, thank you to everyone in advance for your help!

Sheet 1 (Called €œSummary€�)

B C D E F
2 Name Percent Name Percent
3 1.87 115.47
4
5
6
7
8
9


Sheet 2 (Called €œData€�)

A B C D E F G
6 Name Percent Total Percent
7 Jen 2.15 True 2646 117.6
8 Rob 1.99 True 172 142.2
9 Tony 2.52 True 3984 109.9
10 Roe 0.99 False 3599 118.98
11 John 1.94 True 4325 108.13
12 Phil 0.49 False 470 104.44
13 Kurt 1.25 False 931 103.44
14 Vic 4.31 False 0 0
16 Jules 5.45 True 1190 91.54

Lookup using multiple sheets and multiple criteria, sorry if 2 pos
 

Here's one play which delivers on both of your 2 orders ..
(kindly refrain from multi-posting. just post in one newsgroup will do.)

In Summary,
It's presumed that cols A and D are blank to begin with

Put in A4:
=IF(Data!B6="","",IF(AND(Data!B6$C$3,Data!C6),ROW (),"")
(Leave A1:A3 blank) This is the 1st criteria col

Put in B4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(Data!A:A,SMALL($ A:$A,ROW(A1))+2))
Copy B4 to C4

Put in D4:
=IF(Data!F6="","",IF(AND(Data!F6=1000,Data!G6<$F$ 3),ROW(),""))
(Leave D1:D3 blank) This is the 2nd criteria col

Put in E4:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(Data!A:A,SMALL($ D:$D,ROW(A1))+2))
Copy E4 to F4

Then just select A4:F4 and copy down to cover the max expected extent of
source data in "Data", eg copy down to say, F100. You'd get the results
appearing in cols B, C and in cols E, F exactly as required, with all result
lines neatly bunched at the top. Hide away cols A and D, if desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kjguillermo" wrote:
I need to look up and populate sheet one with matching criteria on sheet 2. I
need two separate formulas to do the following:
Formula 1:
If Column B on €œData€� Sheet is Greater than Cell C3 on Summary Sheet AND
Column C on €œData€� Sheet indicates €œTRUE€�, I need the €œName€� (from column A)
and €œPercent€� (from column B) on €œData€� Sheet to populate in the €œName€�
(column B) and €œPercent€� (column C) on the €œsummary Sheet€�. For example,
according to the data below, only Jen, Rob, Tony, John, and Jules, along with
their corresponding percentage will show up in columns B and C on the
€œSummary€� Sheet.

Formula 2:
If Column F on the €œData€� sheet is equal to or greater than 1000, AND
Column €œG€� on the €œData€� Sheet is less than Cell F3 on the €œSummary€� Sheet, I
need the I need the €œName€� (from column A) and €œPercent€� (from column F) on
€œData€� Sheet to populate in the €œName€� (column E) and €œPercent€� (column F) on
the €œSummary€� Sheet. For example, according to the data below only Tony,
John, and Jules along with their corresponding percentage will show up in
columns E and F on the €œSummary€� Sheet.

As always, thank you to everyone in advance for your help!

Sheet 1 (Called €œSummary€�)

B C D E F
2 Name Percent Name Percent
3 1.87 115.47
4
5
6
7
8
9


Sheet 2 (Called €œData€�)

A B C D E F G
6 Name Percent Total Percent
7 Jen 2.15 True 2646 117.6
8 Rob 1.99 True 172 142.2
9 Tony 2.52 True 3984 109.9
10 Roe 0.99 False 3599 118.98
11 John 1.94 True 4325 108.13
12 Phil 0.49 False 470 104.44
13 Kurt 1.25 False 931 103.44
14 Vic 4.31 False 0 0
16 Jules 5.45 True 1190 91.54

Lookup using multiple sheets and multiple criteria, sorry if 2
 

For easy reference,
here's a sample file with the implemented construct:
http://www.savefile.com/files/411923
MultiCriteria Extract wo blank rows.xls
(full details, nicely rendered, as usual! <g)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Lookup using multiple sheets and multiple criteria, sorry if 2
 

Thanks so much for your help Max. I was a little confused with your answer,
but the link to your example did the trick. THis worked like a charm. I
apologize for posting twice. I didn't know if it went through or not. I tried
to put that in my post topic, but I ran out of letters. " Lookup using
multiple sheets and multiple criteria, sorry if 2". Thanks again!!!

"Max" wrote:

For easy reference,
here's a sample file with the implemented construct:
http://www.savefile.com/files/411923
MultiCriteria Extract wo blank rows.xls
(full details, nicely rendered, as usual! <g)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Lookup using multiple sheets and multiple criteria, sorry if 2
 

You're welcome. Glad it helped.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
kjguillermo wrote:
Thanks so much for your help Max. I was a little confused with your answer,
but the link to your example did the trick. THis worked like a charm. I
apologize for posting twice. I didn't know if it went through or not. I tried
to put that in my post topic, but I ran out of letters. " Lookup using
multiple sheets and multiple criteria, sorry if 2". Thanks again !!!