If statement with some sort of lookup function
 

The table below is a lookup for significant timepoints for the parameters
P-x. A table elsewhere has headings relating to parameter name, and a
sequential time column, from 30 to 1440 minutes. The next column along needs
a "*" if the parameter and time match, empty if not.
This worked, but only for the last timepoint (so in the P-c table, for
example, I got a "*" at 1350 minutes, all other cells blank):

=IF(LOOKUP($B$53,stats_lookup!$J$2:$J$37,stats_loo kup!$K$2:$K$37)=A100,"*","")

Any ideas on how to fill every relevant timepoint for a given parameter
(also there may be no significant points for a given parameter).

thanks :-)


P-a 120
P-a 750
P-a 870
P-b 420
P-b 840
P-b 1230
P-b 1350
P-c 420
P-c 750
P-c 1230
P-c 1350
P-d 90
P-d 300
P-d 1080
P-e 60
P-e 150
P-e 240
P-e 330
P-e 870

If statement with some sort of lookup function
 

I've managed to come up with this by concatenating the parameter name and time;

=IF((VLOOKUP(($B$53&A56),stats_lookup!$P$39:$P$74, 1,FALSE)=($B$53&A56)),"*","")

which gives me "*" in the right places, but "#N/A" elsewhere. Any idea how
to get of the errors?


"foilfencingandy" wrote:

The table below is a lookup for significant timepoints for the parameters
P-x. A table elsewhere has headings relating to parameter name, and a
sequential time column, from 30 to 1440 minutes. The next column along needs
a "*" if the parameter and time match, empty if not.
This worked, but only for the last timepoint (so in the P-c table, for
example, I got a "*" at 1350 minutes, all other cells blank):

=IF(LOOKUP($B$53,stats_lookup!$J$2:$J$37,stats_loo kup!$K$2:$K$37)=A100,"*","")

Any ideas on how to fill every relevant timepoint for a given parameter
(also there may be no significant points for a given parameter).

thanks :-)


P-a 120
P-a 750
P-a 870
P-b 420
P-b 840
P-b 1230
P-b 1350
P-c 420
P-c 750
P-c 1230
P-c 1350
P-d 90
P-d 300
P-d 1080
P-e 60
P-e 150
P-e 240
P-e 330
P-e 870

If statement with some sort of lookup function
 

I'm sure this isn't very elegant, but seems to work:

=IF(ISERROR(IF((VLOOKUP(($B$53&A56),stats_lookup!$ P$39:$P$74,1,FALSE)=($B$53&A56)),"*","")),"","*")

"foilfencingandy" wrote:

I've managed to come up with this by concatenating the parameter name and time;

=IF((VLOOKUP(($B$53&A56),stats_lookup!$P$39:$P$74, 1,FALSE)=($B$53&A56)),"*","")

which gives me "*" in the right places, but "#N/A" elsewhere. Any idea how
to get of the errors?


"foilfencingandy" wrote:

The table below is a lookup for significant timepoints for the parameters
P-x. A table elsewhere has headings relating to parameter name, and a
sequential time column, from 30 to 1440 minutes. The next column along needs
a "*" if the parameter and time match, empty if not.
This worked, but only for the last timepoint (so in the P-c table, for
example, I got a "*" at 1350 minutes, all other cells blank):

=IF(LOOKUP($B$53,stats_lookup!$J$2:$J$37,stats_loo kup!$K$2:$K$37)=A100,"*","")

Any ideas on how to fill every relevant timepoint for a given parameter
(also there may be no significant points for a given parameter).

thanks :-)


P-a 120
P-a 750
P-a 870
P-b 420
P-b 840
P-b 1230
P-b 1350
P-c 420
P-c 750
P-c 1230
P-c 1350
P-d 90
P-d 300
P-d 1080
P-e 60
P-e 150
P-e 240
P-e 330
P-e 870