Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Lookup function gives wrong values occasionally | Excel Discussion (Misc queries) | |||
Help using lookup function | Excel Discussion (Misc queries) | |||
Lookup function problem (kg) | Excel Worksheet Functions | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |