VlOOKUP/MATCH/INDEX

[Daniel Q.]

im trying to use vlookups, match and index but the more i work with them the
more confused i get.

i have a "database" setup in one worksheet. I put an IF statement that says
that if i enter info with today's date A1 will give me "NEW", if not "OLD".

On the second worksheet i would like the formula to go to the "db" (array)
and look for any that say "new" and give me values from certain cells
(vlookup?).

For example, i would like the formula to just give me info from Columns C-F
and Rows 7-9.


DB Worksheet
A B C D E F
1 Old/New Date State City Volume Giving Month
2 OLD 15-Apr TX Hou 30000 Apr
3 OLD 15-Apr TX Dal 1500 Apr
4 OLD 15-Apr CA San Die 2000 Apr
5 OLD 15-Apr ND Fargo 2500 Apr
7 NEW 16-Apr CA LA 6000 Apr
8 NEW 16-Apr TX San Ant 1000 Apr
9 NEW 16-Apr TX Austin 7000 Apr


Thanks for all your help in advance!!

 

[Lars-Åke Aspelin]

im trying to use vlookups, match and index but the more i work with them the
more confused i get.

i have a "database" setup in one worksheet. I put an IF statement that says
that if i enter info with today's date A1 will give me "NEW", if not "OLD".

On the second worksheet i would like the formula to go to the "db" (array)
and look for any that say "new" and give me values from certain cells
(vlookup?).

For example, i would like the formula to just give me info from Columns C-F
and Rows 7-9.


DB Worksheet
A B C D E F
1 Old/New Date State City Volume Giving Month
2 OLD 15-Apr TX Hou 30000 Apr
3 OLD 15-Apr TX Dal 1500 Apr
4 OLD 15-Apr CA San Die 2000 Apr
5 OLD 15-Apr ND Fargo 2500 Apr
7 NEW 16-Apr CA LA 6000 Apr
8 NEW 16-Apr TX San Ant 1000 Apr
9 NEW 16-Apr TX Austin 7000 Apr


Thanks for all your help in advance!!

Here is a formula that you can try:

=IF(ROW()>COUNTIF($A$1:$A$10,"=NEW"),"",INDEX(C$1:C$10,SMALL(($A$1:$A$10="NEW")*ROW($A$1:$A$10),ROW()+COUNTIF($A$1:$A$10,"<>NEW"))))

Enter the formula as an array formula, i.e. with CTRL+SHIFT+ENTER.

$A1:$A10 can be changed (on all 4 places) to represent the actual
number of rows in your "db".

Put this on row one (important) and in the column where you want the
info from the C column to be placed. Then copy to the right to handle
info from columns E and F as well.
Then copy down that many rows that you need to present all "NEW" data.

The first part of the formula, before INDEX, can be skipped if you
don't mind #NUM! errors for the target cells without any NEW data.

Hope this helps / Lars-Åke

 

[Brad Vogt]

That works like a charm Lars-Ã…ke. It might be worth your time to name the
range that has the first column of data in it OLDNEW so that the formula can
be put into another sheet easily with the absolute reference and it is easier
to understand.

ie.
=IF(ROW()>COUNTIF(OLDNEW,"=NEW"),"",INDEX(C$1:C$10,SMALL((OLDNEW="NEW")*ROW(OLDNEW),ROW()+COUNTIF(OLDNEW,"<>NEW"))))

To name that range of cells, select them, click up in the name box to the
left of the formula bar, type OLDNEW, then hit enter. Also, if you want this
data extracted to another sheet, you will need to use Sheet1!C$1:C$8 instead
of just C$1:C$10.