excel - If statement with VLookup -

-

i having trouble setting function joins 2 sheets on multiple criteria.

i want following happen in flight column:

if sheet1.product = sheet2.product , sheet1.date >= sheet2.start date , sheet1.date <= sheet2.end date sheet2.flight

i cannot concatenate , vlookup off because looking range of dates , cannot use if(and( because need 'value_if_true' dynamic.

what best formula achieve have tried explain? 

sheet1 flight column row 1 =if(and(d2=sheet2!a2,sheet1!a2>=sheet2!b2,sheet1!a2<=sheet2!c2),sheet2!d2)  date    start date  end date    product      flight 11/29/2015  11/29/2015  12/5/2015   product1    1 11/29/2015  11/29/2015  12/5/2015   product1     11/30/2015  11/29/2015  12/5/2015   product1     11/30/2015  11/29/2015  12/5/2015   product1     12/1/2015   11/29/2015  12/5/2015   product1     12/1/2015   11/29/2015  12/5/2015   product1     12/2/2015   11/29/2015  12/5/2015   product1     12/3/2015   11/29/2015  12/5/2015   product1     12/3/2015   11/29/2015  12/5/2015   product1     12/4/2015   11/29/2015  12/5/2015   product1     12/5/2015   11/29/2015  12/5/2015   product1     11/25/2015  11/29/2015  12/5/2015   product2     11/26/2015  11/29/2015  12/5/2015   product2     11/27/2015  11/29/2015  12/5/2015   product2     11/29/2015  11/29/2015  12/5/2015   product2     11/29/2015  11/29/2015  12/5/2015   product2     11/30/2015  11/29/2015  12/5/2015   product2     11/30/2015  11/29/2015  12/5/2015   product2     12/1/2015   11/29/2015  12/5/2015   product2     12/1/2015   11/29/2015  12/5/2015   product2     12/2/2015   11/29/2015  12/5/2015   product2     12/2/2015   11/29/2015  12/5/2015   product2     12/3/2015   11/29/2015  12/5/2015   product2     12/3/2015   11/29/2015  12/5/2015   product2     12/4/2015   11/29/2015  12/5/2015   product2     12/4/2015   11/29/2015  12/5/2015   product2     12/5/2015   11/29/2015  12/5/2015   product2     12/6/2015   11/29/2015  12/5/2015   product2      sheet2 product   start date    end date    flight product1    11/29/2015  12/1/2015   1 product1    12/2/2015   12/5/2015   2 product2    11/25/2015  11/30/2015  1 product2    12/1/2015   12/2/2015   2 product2    12/3/2015   12/6/2015   3

any appreciated. thanks.

error when changing column reference

getting #n/a! value not available error when change column reference d o. cell references identical (dataval dropdown same source) data below illustrate. mentioned in comments, formula worked before made 1 change of column d o.

formula (using ctrl-shift-enter):

=index('flighting schedule'!$f$3:index('flighting schedule'!f:f,match(1e+99,'flighting schedule'!f:f)),match(1,if((a2>='flighting schedule'!$b$3:index('flighting schedule'!b:b,match(1e+99,'flighting schedule'!f:f)))(a2<='flighting schedule'!$c$3:index('flighting schedule'!c:c,match(1e+99,'flighting schedule'!f:f)))(o2='flighting schedule'!$a$3:index('flighting schedule'!a:a,match(1e+99,'flighting schedule'!f:f))),1,0),0))

this data on active sheet:

date    colb    colc    start date   end date   colf    colg    colh    coli        colj    colk    coll    colm    flight  product 11/29/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 1   1524    #ref!   producta 11/29/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 691 2046191 #ref!   producta 11/30/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 3   1906    #ref!   producta 11/30/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 1152    2923570 #ref!   producta 12/1/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 2   3   #ref!   producta 12/1/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   49  #ref!   producta 12/2/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   2   #ref!   producta 12/3/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   433 #ref!   producta 12/3/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 642 2002431 #ref!   producta 12/4/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 2   24  #ref!   producta

this data on 'flighting schedule'

product  start date  end date       budget      rate flight producta    11/29/2015  11/30/2015  $10,000.00  $5.00   1 producta    12/01/2015  12/03/2015  $10,000.00  $5.00   2 producta    12/04/2016  12/06/14    $10,000.00  $5.00   3

what want array formula then:

=index(sheet2!$d$2:index(sheet2!d:d,match(1e+99,sheet2!d:d)),match(1,if((a2>=sheet2!$b$2:index(sheet2!b:b,match(1e+99,sheet2!d:d)))*(a2<=sheet2!$c$2:index(sheet2!c:c,match(1e+99,sheet2!d:d)))*(d2=sheet2!$a$2:index(sheet2!a:a,match(1e+99,sheet2!d:d))),1,0),0))

being array formula 1 must use ctrl-shift-enter when done editing formula instead of enter or tab. if done correctly excel put {} around formula.

array formulas calculations exponential want limit formula extents of data. index(sheet2!d:d,match(1e+99,sheet2!d:d)) doing. find last cell in column d has number , sets bottom reference automatically. table on sheet 2 grows or contracts reference, using calculations necessary.

enter image description here


Comments

Post a Comment

Popular posts from this blog

ios - Memory not freeing up after popping viewcontroller using ARC -

-
hi project arc based, using uinavigationcontroller make transition between viewcontroller. using profiler analyse happening behind scene memory. noticed when push viewcontroller allocate memory components , when pop not freeing allocated memory. since using arc unable implement dealloc or release component. have analysed in detail , there no memory leak in project. i not using strong property push viewcontroller. here how pushing viewcontroller. viewcontroller *obj = [[viewcontroller alloc] init]; [self.navigationcontroller pushviewcontroller:obj animated:no]; any clue whats going on? should free memory have consumed. please advise your description indicates have retain cycle, leads objects not being deallocated. typical source of retain cycles properties assigned loading nib files (usually declared iboutlet ). two strategies break them , release objects: declare property weak : @property (nonatomic, weak) iboutlet uilabel *statuslabel; set property nil in...
Read more

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more